While still in nf edit the line primary_conninfo to appear something like the following: primary_conninfo = 'dbname=postgres user=postgres host=localgost port=5432 sslmode=disable' $ pg_basebackup -h localhost -U rep_user -X stream -C -S replica_1 -v -R -W -D $HOME/pg/repĪfter this directory has been created, open $HOME/pg/rep/nf and set the port number to something other than the PostgreSQL default 5432. Run the following command to create a backup of the primary server which will then be used as the data directory for our replica server (located at $HOME/pg/rep). Then, append the following to the end of $HOME/pg/data/pg_hba.conf host replication rep_user localhost md5

Open $HOME/pg/data/nf and ensure listen_addresses = 'localhost' Postgres=# create table t1(a int, b int) postgres=# CREATE ROLE rep_user WITH REPLICATION LOGIN postgres=# PASSWORD 'rep_pass' Once in the shell, create a new role for the replication, create a table in the primary server, insert some data into this table and then logout. Let’s start by logging into the primary server as a superuser, typically this is “postgres” the default user. This first example will go over physical replication using streaming, followed by a shorter example with physical replication using shipping, then an example of logical replication, ending with a summary of the different replication types and their features. We are going to use as our data directory $HOME/pg/data. Prerequisitesįor these examples, we used Ubuntu 22.10 to run PostgreSQL 16 (development version).

We will not assume any of that here, the only prerequisite is that you already have Postgres installed and can login to the interactive shell. This can be difficult with replicas as many of the resources readily available assume the user has some PostgreSQL experience and access to another server on which to run the replica.

I believe it is essential when learning a new technology to go through examples on one’s own machine in order to solidify concepts. This blog is aimed at beginners who want to practice the fundamentals of database replication in PostgreSQL but who might not have access to a remote server.

Yum psql