How should I configure my postgresql.conf file to backup the database once per hour?

Question

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.

• user1822: WAL is only generated if you change something in the database. But a completely empty pg_wal seems rather strange. But using "WAL archiving" is not the right way to do a backup "once an hour". You should probably look into backup tools like pgbackrest, barman or pg_probackup
• user203748: When would you use WAL archiving in contrast to the tools you've mentioned? For example: what if I wanted to back up the database once every fifteen minutes?
• user1822: WAL archiving is used for continuous "backup" if you don't want to lose any transactions. But - as jjanes already stated - the files themselves are not a backup. You always need a "base backup" to which those WAL files can be applied to make a full restore.

Answer 1

The WAL archive is not itself a backup. Each WAL file is a list of changes to be applied, but you need to start out with something to apply it to, a "base backup". You can create one of those most simply with "pg_basebackup". This will not be configured in postgresql.conf, as it is an external tool.

Once you have a base backup, you can configure a WAL switch once per hour. This is essentially an hourly "incremental backup". You have apparently configured this already, but you may have done it to the wrong system.

Also, the /etc/postgresql/10/main/pg_wal directory does not contain any files.

Postgresql will not run without files in pg_wal. It is likely that your running server is not really based out of "/etc/postgresql/10/main/". If you can connect via psql, what does show data_directory; reveal?

Answer 2

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