The most efficient replication method in PostgreSQL is using Write-Ahead Logging (WAL), which offers excellent performance through asynchronous replication. In this setup, the primary database server operates in archiving mode, continuously writing WAL files to storage and transferring them to the standby server. The standby server, running in recovery mode, promptly receives these files after they are written. The platform offers two automated methods for setting up a PostgreSQL cluster:<\/p>\n The quickest and most straightforward way to create a PostgreSQL cluster is to use the pre-packaged solution from the marketplace.<\/p>\n Once the setup is complete, you’ll receive a notification with the necessary data for accessing the PostgreSQL administration interface. This information will also be sent via email.<\/p>\n The PostgreSQL database cluster can be enabled via the embedded Auto-Clustering<\/strong> feature on the dashboard. This method provides more customization options compared to the marketplace solution while still automating the configuration process.<\/p>\n Next, utilize the customization capabilities of the wizard to adjust the number of nodes per layer, allocate additional resources, and add other software stacks to your environment.<\/p>\n Below is some helpful information on managing a PostgreSQL cluster:<\/p>\n If Pgpool-II nodes were not included in the cluster topology, use the Primary node to access the cluster. If the load balancing layer is deployed in front of the database cluster, you can use any of the Pgpool-II nodes as the entry point.<\/p>\n In PaaS, the PostgreSQL cluster components can be managed either via CLI or UI.<\/p>\n Pgpool-II nodes can be managed via a user-friendly built-in Administration Panel called pgpoolAdmin. This panel allows you to configure:<\/p>\n Let\u2019s take a look at the primary node configuration parameters used in auto-clustering.<\/p>\n 1. Locate the Primary Node:<\/strong> In your environments list, find the environment containing the primary database. Click the Config<\/strong> button next to the PostgreSQL Primary node.<\/p>\n 2. Open Configuration File:<\/strong> Navigate to the conf<\/strong> directory and open the postgresql.conf<\/strong> file.<\/p>\n Modify WAL Settings: The following lines related to the Write-Ahead Logging (WAL) files can be changed if necessary:<\/p>\n Where:<\/strong><\/p>\n Press the Save<\/strong> button above the editor.<\/p>\n 3. Open the pg_hba.conf<\/strong> configuration file. The standby database connection is permitted by stating the following parameters:<\/p>\n That\u2019s all for primary! Let\u2019s proceed to the standby server\u2019s configuration.<\/p>\n 1. Open the postgresql.conf<\/strong> file, find the Standby Servers section. As you can see this server is acting as standby since the hot_standby<\/strong> parameter is on<\/strong> unlike the primary node where this parameter is commented out.<\/p>\n 2. Scroll down to the end of the config file. There is a primary_conninfo<\/strong> parameter that specifies the connection string which the standby server will use to connect to the sending server. The connection string must indicate the host name (or address) of the sending server, as well as the port number. The username corresponding to the role with the appropriate privileges on the sending server is also provided. The password must also be specified in the primary_conninfo or in a separate ~\/.pgpass file on the backup server if the sender requires password authentication.<\/p>\n 3. The last option that makes database server as secondary is standby.signal<\/strong> file availability, which indicates the server should start up as a hot standby. File must be located in the PostgreSQL data directory and it can be empty or contain any information. Once a secondary is promoted to primary this file will be deleted.<\/p>\n Note:<\/strong> Remember that when modifying certain options, the server may need to be restarted. You can do this in two ways:<\/p>\n 1. Access the phpPgAdmin<\/strong> panel for your primary<\/strong> database by clicking the “Open in Browser<\/strong>” button next to it.<\/p>\n 2. Log in using the database credentials you received via email earlier, and proceed to create a new database.<\/p>\n 3. Next, open the admin panel of your standby<\/strong> database server (similarly to how you accessed the primary one) and verify if the new database has been replicated successfully.<\/p>\n Automatic failover in the PostgreSQL cluster relies on the Pgpool-II node and is activated when it detects the primary database’s failure. This feature is unavailable in topologies lacking Pgpool-II, necessitating manual configuration. Upon identifying a primary database outage, the load balancing node promotes a standby to the primary role. Upon the primary’s recovery, it automatically rejoins the cluster as a standby, with missing data restored via the pgrewind utility.<\/p>\n PostgreSQL lacks native automatic failover capabilities, but third-party solutions offer options for ensuring high availability. Alternatively, users can devise custom failover solutions to address database cluster failures. While numerous failure scenarios exist, we focus on a common workflow for automating failover.<\/p>\n![\"PostgreSQL](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/PostgreSQL-Database-Replication.png\" "\\"PostgreSQL")
<\/a><\/p>\n
\nNow, let\u2019s explore how to install and configure a Primary-Secondary PostgreSQL database cluster.<\/p>\nCreating PostgreSQL Primary-Secondary Cluster<\/h2>\n
\n
Pre-Packaged Marketplace Solution<\/h3>\n
1. Access the Marketplace<\/h4>\n
\n
![\"Install](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Install-PostgreSQL.png\" "\\"Install")
<\/a><\/p>\n2. Configure the Cluster<\/h4>\n
\n
![\"Configure](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Configure-cluster.png\" "\\"Configure")
<\/a><\/p>\n3. Wait for Setup<\/h4>\n
\n
![\"Admin](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Configure-cluster-1.png\" "\\"Admin")
<\/a><\/p>\nTopology Wizard Auto-Clustering<\/h3>\n
1. Initiate Auto-Clustering<\/h4>\n
\n
![\"Auto](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Auto-clustering.png\" "\\"Auto")
<\/a><\/p>\n2. Create the Cluster<\/h4>\n
\n
![\"Running](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Running-cluster.png\" "\\"Running")
<\/a><\/p>\nManaging PostgreSQL Cluster<\/h2>\n
\n
Cluster Entry Point<\/h3>\n
Cluster Admin Panels<\/h3>\n
Database Management<\/h4>\n
\n
![\"Admin](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Admin-panel-1.png\" "\\"Admin")
<\/a><\/p>\nPgpool-II Management<\/h4>\n
![\"pggpoolAdmin](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/PGgpoolAdmin-Dashboard.png\" "\\"pggpoolAdmin")
<\/a><\/p>\n\n
Primary PostgreSQL Configuration<\/h3>\n
![\"postgresql.conf\"](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/postgresql.conf_.png\" "\\"postgresql.conf\\"")
<\/a><\/p>\n\r\nwal_level = hot_standby\r\nmax_wal_senders = 10\r\narchive_mode = on\r\narchive_command = 'cd .'<\/code><\/pre>\n\n
\n
\r\narchive_command = 'test ! -f \/var\/lib\/pgsql\/data\/pg_wal\/%f && cp %p \/tmp\/mydata\/%f'<\/code><\/pre>\n![\"Modify](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Modify-WAL-settings.png\" "\\"Modify")
<\/a><\/p>\n\r\nhost replication all {standby_IP_address}\/32 trust<\/code><\/pre>\n![\"pg_hbs.conf\"](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/pg_hbs.conf_.png\" "\\"pg_hbs.conf\\"")
<\/a><\/p>\nConfiguring Standby<\/h3>\n
![\"Standby](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Standby-server.png\" "\\"Standby")
<\/a><\/p>\n\r\nprimary_conninfo = 'host=primary_server_ip port=5432 user=replication_user password=your_password'<\/code><\/pre>\n\n
![\"Restart](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Restart-node-option.png\" "\\"Restart")
<\/a><\/p>\n\n
\r\nsudo service postgresql restart<\/code><\/pre>\n![\"CLI](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/CLI-for-restart-server.png\" "\\"CLI")
<\/a><\/p>\nReplication Check<\/h3>\n
![\"Create](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Create-new-DB.png\" "\\"Create")
<\/a><\/p>\n![\"Verify](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Verify-new-DB.png\" "\\"Verify")
<\/a><\/p>\nAutomatic Failover Scenario<\/h3>\n
Manual Failover Scenario<\/h3>\n
![\"Failover](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Failover-Scenario.png\" "\\"Failover")
<\/a><\/p>\n![\"Protected](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Protected-Server.png\" "\\"Protected")
<\/a><\/p>\n![\"Add](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Add-primary-node.png\" "\\"Add")
<\/a><\/p>\n![\"Add](\"https:\/\/accuweb.cloud\/resource\/wp-content\/uploads\/2024\/05\/Add-formar-primary-node.png\" "\\"Add")
<\/a><\/p>\n