== TIMESTAMPS == 0:00 What are we going to build 2:15 Build and configure Ubuntu 24.04 servers for Postgresql 16 6:20 Postgres installation process 9:20 Configuring pg_hba.conf 10:35 Create database in postgresql 12:10 Create primary and secondary database 14:05 Configuring postgresql.conf file 14:25 why we need to configure ‘listen addresses’ in postgresql.conf 16:35 Further configuration of secondary postgres server 17:10 pg_basebackup command explained and run 25:15 Installing and configuring PgBackrest 26:00 Create SSH connectivity between both servers 32:10 Configuing pgbackrest.conf file 39:00 Creating full postgres backup with pgbackrest command 42:00 Failover from primary to secondary postgres database 43:45 Recover postgres from remote location Please let me know if you have any questions. Marek
Great video Marek! Really liked the detailed way with which you present One question: 1. Suppose pgprimary goes down in production, how to instantly automatically appoint pgsecondary as the new primary (without manually running pgpromote command)? 2. Once pgprimary cames back up, how to again have a primary-standby setup? Basically the requirement is have 2 servers in resilient mode - 1 primary , 1 standby...is there a way to run both the servers, automatically managing primary and secondary
Hi Thank you for watching and commenting! Regarding your questions : 1. I am not aware of any built-in native Postgres features that would allow you to do that, you would need to install additional tool - like Failover Manager - to achieve that : www.enterprisedb.com/docs/efm/latest/ . 2. One your old secondary is promoted to master, you can either destroy old server , build completely new one and run pg_basebackup as in ua-cam.com/video/Yapbg0i_9w4/v-deo.html but this time pointing to your secondary ( as its now acting as a master ) , or you can try and recover the faulty one by stopping the postgres service on it, remove all content of /var/lib/postgresql/16/main/, run pg_basebackup ( also on it pointing to secondary, you can use -P switch to see the progress of that operation ) and once completed - you just start postgres service. So basically your secondary is now master and your primary is now standby- their roles have switched. Hope that helps! Marek
Thank you for such nice video. Where should be run pgBackRest backup command? On primary or on secondary? In this video you have executed pgbackrest backup on primary but how would we know that backup is actually taking from standby? Can you please explain? I am kind of confuse here :(
I know it is confusing. You always run pgbackrest command on primary. In our scenario- the primary starts the process, then all workload is passed to secondary to handle, and at the end of the process it comes back to primary to complete. Primary does minimal work though, most resource consuming task is performed on secondary. You can run some basic commands like 'ps auxxx' or 'htop' to see real time what it looks like. Hope that helps! Marek
== TIMESTAMPS ==
0:00 What are we going to build
2:15 Build and configure Ubuntu 24.04 servers for Postgresql 16
6:20 Postgres installation process
9:20 Configuring pg_hba.conf
10:35 Create database in postgresql
12:10 Create primary and secondary database
14:05 Configuring postgresql.conf file
14:25 why we need to configure ‘listen addresses’ in postgresql.conf
16:35 Further configuration of secondary postgres server
17:10 pg_basebackup command explained and run
25:15 Installing and configuring PgBackrest
26:00 Create SSH connectivity between both servers
32:10 Configuing pgbackrest.conf file
39:00 Creating full postgres backup with pgbackrest command
42:00 Failover from primary to secondary postgres database
43:45 Recover postgres from remote location
Please let me know if you have any questions.
Marek
This was a brilliant tutorial. There isn’t much content like this online. Thank you so much for taking the time to make this.
Glad it was helpful! Thank you for watching and commenting.
Marek
Great video Marek! Really liked the detailed way with which you present
One question:
1. Suppose pgprimary goes down in production, how to instantly automatically appoint pgsecondary as the new primary (without manually running pgpromote command)?
2. Once pgprimary cames back up, how to again have a primary-standby setup?
Basically the requirement is have 2 servers in resilient mode - 1 primary , 1 standby...is there a way to run both the servers, automatically managing primary and secondary
Hi
Thank you for watching and commenting!
Regarding your questions :
1. I am not aware of any built-in native Postgres features that would allow you to do that, you would need to install additional tool - like Failover Manager - to achieve that : www.enterprisedb.com/docs/efm/latest/ .
2. One your old secondary is promoted to master, you can either destroy old server , build completely new one and run pg_basebackup as in ua-cam.com/video/Yapbg0i_9w4/v-deo.html but this time pointing to your secondary ( as its now acting as a master ) , or you can try and recover the faulty one by stopping the postgres service on it, remove all content of /var/lib/postgresql/16/main/, run pg_basebackup ( also on it pointing to secondary, you can use -P switch to see the progress of that operation ) and once completed - you just start postgres service. So basically your secondary is now master and your primary is now standby- their roles have switched.
Hope that helps!
Marek
Thank you for such nice video. Where should be run pgBackRest backup command? On primary or on secondary? In this video you have executed pgbackrest backup on primary but how would we know that backup is actually taking from standby? Can you please explain? I am kind of confuse here :(
I know it is confusing.
You always run pgbackrest command on primary.
In our scenario- the primary starts the process, then all workload is passed to secondary to handle, and at the end of the process it comes back to primary to complete.
Primary does minimal work though, most resource consuming task is performed on secondary.
You can run some basic commands like 'ps auxxx' or 'htop' to see real time what it looks like.
Hope that helps!
Marek