Postgresql pg_basebackup to Repair Replication Streaming

This article is aimed to help someone that needs to run pg_basebackup. The Postgresql documentation is excellent, and hopefully this will serve as an example of successful usage.

Problem

Solution

Stop Postgresql on the Secondary / Replica

-- stop postgres on slave omdb2
$ systemctl stop postgresql-10.service
-- verify dead
$ systemctl status -l postgresql-10.service

Rename the current Postgresql data directory

-- confirm PGDATA (in my case it was "/data/pgsql/data"
-- find yours with "find / -name postgresql.conf"
-- the directory containing postgresql.conf should be your $PGDATA
$ echo $PGDATA
-- on slave, rename $PGDATA directory
-- this clears the way for a new data directory without trashing the prior one
-- use your $PGDATA directory path (not mine (below))
$ mv /data/pgsql/data /data/pgsql/data-backup
-- create the fresh new data directory for $PGDATA & chmod & chown
-- use your $PGDATA directory path (not mine (below))
$ mkdir -p /data/pgsql/data
$ chown -R postgres:postgres /data/pgsql/data
$ chmod 700 /data/pgsql/data

Initiate pg_basebackup from Secondary / Replica

-- start pg_basebackup from slave$ /usr/pgsql-10/bin/pg_basebackup -h <host-of-master> -D $PGDATA -R -P -v -U replica-- this will prompt you for the replica password, if you don't know it, it is likely stored in $PGDATA/recovery.conf, assuming you had streaming replication working sometime in the past

Confirm Ownership of $PGDATA

-- make sure the ownership is good
-- use your $PGDATA directory path (not mine (below))
$ chown -R postgres:postgres /data/pgsql/data

Start up the Secondary / Replica

-- start pg
$ systemctl start postgresql-10.service
-- check it
$ systemctl status -l postgresql-10.service

Final Thoughts

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store