Skip to content

Instantly share code, notes, and snippets.

@greinacker
Last active September 11, 2023 01:10
Show Gist options
  • Save greinacker/4968619 to your computer and use it in GitHub Desktop.
Save greinacker/4968619 to your computer and use it in GitHub Desktop.
PostgreSQL 9.2.x replication
hostssl replication replicator 5.6.7.8 md5
listen_address = # make sure we're listening as appropriate
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
hot_standby = on
echo Stopping PostgreSQL
sudo service postgresql stop
echo Cleaning up old cluster directory
sudo -u postgres rm -rf /var/lib/postgresql/9.2/main
echo Starting base backup as replicator
sudo -u postgres pg_basebackup -h 1.2.3.4 -D /var/lib/postgresql/9.2/main -U replicator -v -P
echo Writing recovery.conf file
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.2/main/recovery.conf <<- _EOF1_
standby_mode = 'on'
primary_conninfo = 'host=1.2.3.4 port=5432 user=replicator password=thepassword sslmode=require'
trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"
echo Startging PostgreSQL
sudo service postgresql start
sudo -u postgres psql -x -c "select * from pg_stat_replication;"
sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'thepassword';"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment