The article is divided into the following sections:

• Considerations to keep in mind
• PostgreSQL Installation
• Binary Replication
• Logical Replication
• Binary Replication Check
• Logical Replication Check
• Promote SECONDARY - Binary Replication
• Promote SECONDARY - Logical Replication

Considerations to keep in mind:

In any PostgreSQL server, queries are always written to a WAL file before being executed in the DB (regardless of whether there is replication or not).

PostgreSQL supports three forms of replication:

• Binary/Streaming Replication: Binary replication through real-time WAL file streaming (recommended).
• Logical Replication: The WAL files are decoded and the queries are sent to the SECONDARY (recommended only when PostgreSQL versions, OS, or LIBC differ).
• pglogical : Third-party project to provide logical replication in PostgreSQLs with versions lower than 10.4 (not recommended).

The servers involved in this manual are as follows:

PostgreSQL00: 192.168.69.22
PostgreSQL01: 192.168.69.23

If we are installing them on a FreeBSD jails-based system like in my case with Bastille , PostgreSQL will give an error about sysvipc:

2023-03-02 23:51:17.310 CET [63048] DETAIL:  Failed system call was shmget(key=65282, size=56, 03600).

We allow access by jail, it can also be allowed globally on the parent host for all jails but it is preferable to be more granular:

NOTE: Do not use allow.sysvipc as it is considered deprecated and grants more permissions than strictly necessary.

PostgreSQL Installation:

Now we can install PostgreSQL without any problems:

Initialize the database:

Enable and start the service:

Create the database and test table on the PRIMARY:

postgres=# CREATE DATABASE test_db;
postgres=# \c test_db;
test_db=# CREATE TABLE test( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);

Create a regular access user on the PRIMARY:

test_db=# CREATE USER kr0m LOGIN ENCRYPTED PASSWORD 'PASSWORD';
test_db=# GRANT ALL PRIVILEGES ON test TO kr0m;

Allow access for user kr0m:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         kr0m            192.168.69.0/24         md5

Reload the configuration:

Create the replication user on the PRIMARY:

If we are going to use binary replication or logical replication where we will indicate the tables to replicate in the publication, the replication user must be created with the role: REPLICATION.

On the other hand, if we are going to use logical replication but we are going to replicate all the tables of the DB with a “FOR ALL TABLES” publication, the replication user must be created with the role: SUPERUSER.

In logical replication, we will indicate the tables one by one or simply use “FOR ALL TABLES” depending on whether we want a more or less granular access.

test_db=# CREATE USER rep_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'POSTGRE_REP_PASS';
test_db=# ALTER ROLE rep_user CONNECTION LIMIT -1;
test_db=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 kr0m      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rep_user  | Replication                                                | {}

test_db=# CREATE USER rep_user WITH SUPERUSER LOGIN ENCRYPTED PASSWORD 'POSTGRE_REP_PASS';
test_db=# ALTER ROLE rep_user CONNECTION LIMIT -1;
test_db=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 kr0m      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rep_user  | Superuser                                                  | {}

Binary replication:

The biggest disadvantage of this replication system is that the entire server has to be replicated, DBs/Tables cannot be chosen.

We allow replication user access on both the PRIMARY and SECONDARY, so if we change the roles, we only need to resynchronize the reinstalled server:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     rep_user        SECONDARY_IP/32             md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     rep_user        PRIMARY_IP/32            md5

Reload the configuration:

By default, in FreeBSD, the PostgreSQL wall_level is already configured with the replica level:

 wal_level 
-----------
 replica
(1 row)

Remove any previous data on the SECONDARY:

Load the backup on the SECONDARY:

Start the service:

Logical replication:

It works through a table-level publication/subscription system, the main advantages are:

• Replication between different major versions of PostgreSQL. -> Migration between PostgreSQL versions, it is the only way to avoid the collations problem .
• Replication between PostgreSQL instances on different platforms. -> Migration from Linux to FreeBSD.
• Greater flexibility to replicate only certain commands, for example, not replicating DELETES.
• We can form chains of servers with PRIMARY/SECONDARY in the middle of the chain.

The inconveniences are:

• DDL commands , such as create database, rename table, and others, are not replicated. Since DDL commands are not replicated, the table structure of a PRIMARY does not have to be the same as that of the SECONDARY, as long as there is no conflict. For example, columns that the PRIMARY does not have could be added to the SECONDARY. A recommendation is to make changes first in the SECONDARY and then in the PRIMARY, so there will never be any problems.
• Autogenerated IDs could be different between the PRIMARY/SECONDARY. For example, if the table has auto-incremental fields, the SECONDARY had data, and an INSERT is executed in the PRIMARY, the INSERT will be made in the SECONDARY, but the incremental ID will be different.
• It does not support the replication of views, materialized views, partition root tables, or foreign tables.
• The pg_hba.conf and the users created by the PostgreSQL CLI must be manually configured on both servers.

This replication is recommended for migrations between OS versions and setting up everything with the new OS through binary replication. It is only an intermediate transition step.

We must bear in mind that logical replication allows two types of publications: one in which we indicate table by table the tables to be published, and another mode in which we indicate all the tables that the database contains without the need to specify them (FOR ALL TABLES). Depending on the type of publication, the steps to follow vary slightly.

We allow replication user access to each of the databases to be replicated, in this case test_db, on both the PRIMARY and the SECONDARY. This way, if we change roles, we only need to resynchronize the reinstalled server:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         rep_user        SECONDARY_IP/32             md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    test_db         rep_user        PRIMARY_IP/32            md5

If we are going to indicate which tables we want to replicate, the replication user only needs the SELECT grant on each of the tables. However, if we are going to create the publication using “FOR ALL TABLES”, we will have to give SELECT grants on all tables in the public schema.

We assign the GRANTs on the PRIMARY:

test_db=# GRANT SELECT ON test TO rep_user;

test_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO rep_user;

We reload the configuration:

To use logical replication, we must change the wal_level to logical. It is best to configure it on both the PRIMARY and the SECONDARY, so that if we change the role, everything is ready to reconnect the reinstalled machine:

wal_level = logical

We restart the service:

We take a dump of the structure and users of the databases on the PRIMARY:

We copy the dump to the SECONDARY:

The publications allow us a wide range of possibilities:

CREATE PUBLICATION mypublication FOR TABLE users, departments;                                       -> Replicar varias tablas.
CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);                  -> Replicar los datos de una tabla que cumplan una condición concreta.
CREATE PUBLICATION alltables FOR ALL TABLES;                                                         -> Replicar todas las tablas incluidas las tablas creadas en un futuro(solo afecta al PRIMARY, el SECONDARY sigue necesitando configuración).
CREATE PUBLICATION insert_only FOR TABLE mydata WITH (publish = 'insert');                           -> Replicar solo los INSERTs de una tabla.
CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production; -> Replicar varias tablas y las tablas que estén en el schema(recordad que un schema NO es una DB en PostgreSQL) indicado.
CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;                          -> Replicar las tablas que estén en los schemas indicados.
CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);                              -> Replicar X campos de una tabla.

We are going to create a publication for each database, but taking into account that the template and postgres databases serve special purposes:

• postgres: Database to which PostgreSQL clients connect by default. If we delete it, we must indicate in each connection which database we want to connect to, and many programs will have this default database hardcoded and will stop working.
• template1: A copy is made when creating new databases. It can be modified so that new databases have some default modification we want.
• template0: Same content as template1 if template1 has not been modified. Template0 should never be modified.

We consult the databases in the PRIMARY:

test_db=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 test_db   | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 
(4 rows)

We create the publication of test_db in the PRIMARY, depending on the type of publication we are going to use, we will proceed in one way or another:

test_db=# CREATE PUBLICATION test_db_publication FOR TABLE test;

test_db=# CREATE PUBLICATION test_db_publication FOR ALL TABLES;

If we had more databases, we would create a publication for each of them:

test_db=# \c DB_NAME
DB_NAME=# CREATE PUBLICATION test_db_publication FOR TABLE test;

test_db=# \c DB_NAME
DB_NAME=# CREATE PUBLICATION test_db_publication FOR ALL TABLES;

We can see the publications:

test_db=# \dRp+
                      Publication test_db_publication
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.test"

test_db=# \dRp+
                      Publication test_db_publication
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
----------+------------+---------+---------+---------+-----------+----------
 postgres | t          | t       | t       | t       | t         | f
(1 row)

We load the dump of the schemas and users in the SECONDARY:

We create the subscription in the SECONDARY:

test_db=# CREATE SUBSCRIPTION test_db_subscription CONNECTION 'host=192.168.69.22 dbname=test_db user=rep_user password=POSTGRE_REP_PASS' PUBLICATION test_db_publication;

We can see the subscriptions, whether we use logical replication indicating the tables to publish or if we use “FOR ALL TABLES” the output will be the same in the SECONDARY:

test_db=# \dRs+
                                                                                                          List of subscriptions
         Name         |  Owner   | Enabled |      Publication      | Binary | Streaming | Two-phase commit | Disable on error | Synchronous commit |                                 Conninfo                                  | Skip LSN 
----------------------+----------+---------+-----------------------+--------+-----------+------------------+------------------+--------------------+---------------------------------------------------------------------------+----------
 test_db_subscription | postgres | t       | {test_db_publication} | f      | f         | d                | f                | off                | host=192.168.69.22 dbname=test_db user=rep_user password=POSTGRE_REP_PASS | 0/0
(1 row)

At this point, replication will be working, but we must bear in mind that even if we have configured the publication with “FOR ALL TABLES”, when creating tables in the PRIMARY, they are not replicated automatically. To replicate them, we will always have to create them manually in the SECONDARY and depending on how we have created the publication, execute additional commands.

PRIMARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);
test_db=# ALTER PUBLICATION test_db_publication ADD TABLE NOMBRE_NUEVA_TABLA;
test_db=# GRANT SELECT ON NOMBRE_NUEVA_TABLA TO rep_user;

SECONDARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);
test_db=# ALTER SUBSCRIPTION test_db_subscription REFRESH PUBLICATION;
test_db=# GRANT SELECT ON NOMBRE_NUEVA_TABLA TO rep_user;

PRIMARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);

SECONDARY:
test_db=# CREATE TABLE NOMBRE_NUEVA_TABLA( ID integer PRIMARY KEY NOT NULL, DATA varchar(40) NOT NULL);
test_db=# ALTER SUBSCRIPTION test_db_subscription REFRESH PUBLICATION;

NOTE: In logical replication indicating tables, the GRANT is executed on both nodes, so if we change the ROLE, the GRANTs will already be assigned.

Binary replication check:

We check that it works by creating a database on the PRIMARY and seeing how it is replicated on the SECONDARY.

postgres=# CREATE DATABASE testkr0m;
postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 testkr0m  | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 

On the PRIMARY we can see the SECONDARY connected:

postgres=# \x
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 65460
usesysid         | 16384
usename          | rep_user
application_name | walreceiver
client_addr      | 192.168.69.23
client_hostname  | 
client_port      | 30872
backend_start    | 2023-04-29 16:58:58.961358+02
backend_xmin     | 
state            | streaming
sent_lsn         | 0/348CEE0
write_lsn        | 0/348CEE0
flush_lsn        | 0/348CEE0
replay_lsn       | 0/348CEE0
write_lag        | 00:00:00.000104
flush_lag        | 00:00:00.002399
replay_lag       | 00:00:00.002429
sync_priority    | 0
sync_state       | async
reply_time       | 2023-04-29 17:02:37.54732+02

On the SECONDARY we can see where it reads the data from:

postgres=# \x
postgres=# SELECT pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+------------------------------
pg_is_in_recovery             | t
pg_is_wal_replay_paused       | f
pg_last_wal_receive_lsn       | 0/349C428
pg_last_wal_replay_lsn        | 0/349C428
pg_last_xact_replay_timestamp | 2023-04-29 17:03:22.893567+02

NOTE: If the server is in standalone mode (replication has not been configured), neither of the two queries returns data. If replication is broken, the PRIMARY returns nothing.

Logical replication check:

We check that it works by inserting data on the PRIMARY and seeing how it is replicated on the SECONDARY.

test_db=# INSERT INTO test (ID,DATA) VALUES(11,22);

test_db=# SELECT * FROM test;
 id | data 
----+------
 11 | 22
(1 row)

On the PRIMARY we can see the SECONDARY connected:

postgres=# \x
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 31051
usesysid         | 16391
usename          | rep_user
application_name | test_db_subscription
client_addr      | 192.168.69.23
client_hostname  | 
client_port      | 38663
backend_start    | 2023-05-06 15:39:34.43372+02
backend_xmin     | 
state            | streaming
sent_lsn         | 0/1EDDEE0
write_lsn        | 0/1EDDEE0
flush_lsn        | 0/1EDDEE0
replay_lsn       | 0/1EDDEE0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2023-05-06 16:37:02.234299+02

Promote SECONDARY - Binary Replication:

By default, a SECONDARY is in RO mode (hot_standby), if the PRIMARY has any problems, we must promote the SECONDARY and it will switch to RW mode.

We check if it is a SECONDARY:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

We promote it:

postgres=# SELECT pg_promote();
 pg_promote 
------------
 t
(1 row)

We check that it has been promoted:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

Now if we try to write to the promoted SECONDARY, it will allow us.

Promote SECONDARY - Logical Replication:

In logical replication, the SECONDARY has always been writable, so there is nothing to do, just use it.