Question: How can you replicate only one database in PostgreSQL?

Answer

PostgreSQL's built-in replication solutions, such as streaming replication or logical replication, are designed with a cluster-wide approach, meaning they replicate all databases within a PostgreSQL cluster (instance). However, if you need to replicate only a single database, your best approach is to use logical replication.

Logical Replication

Logical replication allows you to replicate data at the granularity of tables. This means you could effectively replicate all tables from one database, achieving database-level replication. Here's how you can set it up:

On the Source Server (Publisher):

1. Enable Logical Replication: Edit postgresql.conf and set wal_level = logical.

2. Configure Client Authentication: Edit pg_hba.conf to allow connections from the subscriber server.

3. Create Publication:

   CREATE PUBLICATION my_publication FOR ALL TABLES;

   Note: Execute this command for each database you want to replicate.

On the Destination Server (Subscriber):

1. Create Subscription: Assuming the publication was created on a source database named source_db, you would execute:

   CREATE SUBSCRIPTION my_subscription
   CONNECTION 'host=source_host port=5432 dbname=source_db user=replica_user password=replica_pass'
   PUBLICATION my_publication WITH (copy_data = true);

   Execute this command in the destination database where you wish to replicate the source database's tables.

Considerations

• DDL Statements: Logical replication does not automatically replicate schema changes. You must manually apply DDL statements (e.g., CREATE TABLE, ALTER TABLE) on the subscriber database.
• Selective Replication: If there are specific tables you do not wish to replicate, you can create a publication that only includes the desired tables instead of using FOR ALL TABLES.
• Performance Impact: Logical replication can be more resource-intensive than physical replication. Test and monitor performance impacts in your environment.

While PostgreSQL does not natively support single-database physical replication out of the box, logical replication provides a flexible alternative that can meet similar needs with table-level granularity.