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

Hi,

Indeed Continuous Archiving and Point-in-Time Recovery
(https://www.postgresql.org/docs/10/continuous-archiving.html) is the
best way to achieve your goal.

Have you checked the archiver process ? ('ps -ef |grep postgres |grep
archiver'). It should report success or fail.

You could also check that with the system view : 'SELECT * FROM
pg_stat_archiver;'.

Best option is to have a look at the PostgreSQL logs.

Is your /test directory writable by the postgres linux user ?

To help you, there's a few PITR tools that handles backups, restores and
even backup retention. Have a look at https://pgbackrest.org/ ;-)

Kind regards,

On 2/21/20 12:18 PM, mimble9(at)danwin1210(dot)me wrote:
> 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!
>
>
>
>
>
--
Stefan FERCOT
http://dalibo.com - http://dalibo.org