Confused about how to enable backups (e.g. Write Ahead Log).

From: mimble9(at)danwin1210(dot)me
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Confused about how to enable backups (e.g. Write Ahead Log).
Date: 2020-02-21 11:18:47
Message-ID: 2e892aa39c48c34184ab1d97a750a089.squirrel@danielas3rtn54uwmofdo3x2bsdifr47huasnmbgqzfrec5ubupvtpid.onion
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have been attempting, without success, to create a Write Ahead Log (WAL)
backup using PostgreSQL 10.0 under 18.04 Ubuntu VPS.

It might be that I am doing the wrong thing and my method will never work.
In which case: what is the best way to achieve my 'Goal' (below)?

Goal:

I want to backup a database every 15 minutes. Ideally, I want to backup
only if the database has changed (perhaps this isn't possible and backups
happen whether or not the database has changed)?

Attempts:

I edited /etc/postgresql/10/main/postgresql.conf as follows:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /test/%f'
archive_timeout = 900

This should - in my opinion - every 15 minutes backup (cp) the database
(referenced by %p) to the /test/ directory using the filename (referenced
by %f) of the database.

I then:

/etc/init.d/postgresql stop
/etc/init.d/postgresql start

And checked that PostgreSQL is running with /etc/init.d/postgresql status:

postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor
preset: enabled)
Active: active (exited) since Fri 2020-02-21 01:23:59 UTC; 9h ago
Process: 60534 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 60534 (code=exited, status=0/SUCCESS)

Feb 21 01:23:58 server systemd[1]: postgresql.service: Failed to reset
devices.list: Operation not permitted
Feb 21 01:23:58 server systemd[1]: Starting PostgreSQL RDBMS...
Feb 21 01:23:59 server systemd[1]: Started PostgreSQL RDBMS.

However, the /test/ directory did not show any backup files

I then read that I needed to use pg_basebackup to make an initial backup
(before editing postgresql.conf) so I tried pg_basebackup -D /etc/backup.
This created a duplicate of /var/lib/postgresql/10/main/ in /etc/backup
but I'm not sure how this relates to or effects (if it does) the Write
Ahead Log.

I still don't have any backups in /test/ (or anywhere).

Can anyone kindly explain how, exactly, I can achieve my 'Goal' whether
using a modification of this method or something different (but superior).
Many thanks!

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Stefan Fercot 2020-02-21 11:57:23 Re: Confused about how to enable backups (e.g. Write Ahead Log).
Previous Message David G. Johnston 2020-02-20 16:28:00 Re: Outer Right Join?