Postgresql – How should I configure the postgresql.conf file to backup the database once per hour

postgresqlwrite-ahead-logging

My goal is to back up the PostgreSQL 10 database on a Ubuntu 18.04 VPS once per hour.

However, I clearly do not understand how to achieve this. Here is what I've done.

I modify the postgresql.conf file in four ways:

wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
archive_command = 'cp %p /test/%f'  # copy the database file to the /test directory
archive_timeout = 3600            # force a logfile segment switch after this
                                # number of seconds; 0 disables

These are the only entries in postgresql.conf that I've changed. AIUI, I'm saying: copy the current database file every hour to /test using the same filename as the database filename.

I then restart PostgreSQL using /etc/init.d/postgresql reload (I've also tried restart).

However, nothing ever copies to /test. Also, the /etc/postgresql/10/main/pg_wal directory does not contain any files.

What am I doing wrong? Maybe other entries in the file need to be changed? Thanks.

Best Answer

For hourly backups, you can achieve this by scheduling Cronjob to take Pg_dump. Alternatively, you can configure Bart and schedule the backups.

Related Question