1

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.

Improve this question
3
  • 2
    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
    – user1822
    Commented Feb 19, 2020 at 7:35
  • 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?
    – user203748
    Commented Feb 19, 2020 at 21:55
  • 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.
    – user1822
    Commented Feb 19, 2020 at 21:58

2 Answers 2

Reset to default
0

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

Improve this answer
0

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?

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.