How To – Postgres

When and How do you use pg_rewind() in PostgreSQL?

by

The pg_rewind utility in PostgreSQL is used to synchronize a standby server with a new master server in a scenario where the old master server has been promoted to a new master server and the standby server needs to catch up with the new master server’s state. This utility can be used when the old master server still has the transaction logs (WAL) that were generated after the promotion, and it can be used to “rewind” the state of the standby server to the point in time where the promotion occurred.

To use the pg_rewind utility, you need to perform the following steps:

Read more

How do I Re-Attach a Master once a Replica has been Promoted in Postgres

by

To reattach a master server once a replica has been promoted in PostgreSQL, you need to perform the following steps:

  1. On the new master server, edit the postgresql.conf configuration file and set the wal_level parameter to hot_standby. This will enable the new master server to keep a sufficient amount of transaction log data (WAL) to allow the old master server to connect and apply the changes.
  2. Restart the new master server for the changes to take effect.
  3. On the old master server, edit the postgresql.conf configuration file and set the hot_standby parameter to on. This will enable the old master server to connect to the new master server and start receiving the changes.
  4. In the recovery.conf file on the old master server, add the following lines to specify the connection details for the new master server:

Read more

PostgreSQL – How to Create Database

by

To create a new database in PostgreSQL, you can use the CREATE DATABASE statement. Here is the basic syntax for this statement: Replace database_name with the name of the database that you want to create. For example, to create a database named my_database, you would use the following statement: This will create a new database …

Read more

How to set up Streaming Replication in Postgres

by

To set up streaming replication in PostgreSQL, you need to perform the following steps:

  1. Set up the master server and the standby server. The master server is the primary server that will receive all the write operations, while the standby server will receive a copy of all the changes made on the master server and will be used for failover in case the master server becomes unavailable.
  2. On the master server, edit the postgresql.conf configuration file and set the wal_level parameter to hot_standby. This will enable the master server to keep a sufficient amount of transaction log data (WAL) to allow the standby server to connect and apply the changes.
  3. Restart the master server for the changes to take effect.
  4. On the standby server, edit the postgresql.conf configuration file and set the hot_standby parameter to on. This will enable the standby server to connect to the master server and start receiving the changes.
  5. In the recovery.conf file on the standby server, add the following lines to specify the connection details for the master server:

Read more