All about Logical Replication in Postgres.

What is Logical Replication

Logical replication in PostgreSQL is a feature that allows you to replicate data from one database to another, in a flexible and customizable way. Unlike physical replication, which replicates the entire database and its structure, logical replication allows you to specify which tables and columns you want to replicate, and how you want the data to be replicated. This means that you can replicate only the data that is relevant to your application, and you can apply custom transformations or filters to the replicated data to meet your specific needs.

Continue reading “All about Logical Replication in Postgres.”

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

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:

Continue reading “When and How do you use pg_rewind() in PostgreSQL?”

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

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:
Continue reading “How do I Re-Attach a Master once a Replica has been Promoted in Postgres”

PostgreSQL – How to Create Database

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 with the specified name. You can then connect to the database and start creating tables, indexes, and other database objects within it.