Postgresql Point in Time Recovery, WAL and continuous archiving

In postgresql.conf archive set following settings:

# Write Ahead Log section:

# WAL LEVEL
# wal_level = archive, hot_standby (9.1+)
# wal_level =  archive, hot_standby, or logical (9.4+)
# wal_level = replica or logical (9.6+)

wal_level = archive

archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)

# archive_command for copy wal segments to /home/wal/ directory
# dont forget(linux): chown postgres:postgres /home/wal

archive_command = 'test ! -f /home/wal/%f && cp %p /home/wal/%f'     # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
# Windows version:  archive_command = 'copy "%p" "C:\\server\\wal\\%f"'  # Windows

# In replication section:
max_wal_senders = 2

In pg_hba.conf activate user and domain for replication.
I will use postgres user by Unix domain socket file:

local   replication     postgres                                peer

Then, restart Postgresql service.

Now, make the origin point backup, using pg_basebackup tool:

cd /home
mkdir basebackup
chown postgres:postgres basebackup/
su postgres

pg_basebackup -F t -z -x -D /home/basebackup

# pointing Unix domain socket file directory
# using setting: unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
pg_basebackup -h /tmp -F t -z -x -D /home/basebackup

# Another example with host, user, requires password:
# /usr/pgsql-9.1/bin/pg_basebackup -h 127.0.0.1 -F t -z -U sisapp -P -x -D /home/basedump

Documentation:
https://www.postgresql.org/docs/9.1/static/continuous-archiving.html
https://www.postgresql.org/docs/9.5/static/continuous-archiving.html
https://www.postgresql.org/docs/9.6/static/continuous-archiving.html

https://www.postgresql.org/docs/current/static/app-pgbasebackup.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s