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

Thank you for your input,

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

5 | 000000010000000000000004 | 2020-02-21 01:29:56.77076+00 |            0
|                 |                  | 2020-02-21 00:31:12.898026+00


So there is some kind of archive but not one that is regularly updating.

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

Permissions are: drwxr-xr-x 1 root     root        278 Feb 21 01:50 test

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

I've had a look at the FAQ for this tool and it seems suitable. Have you
used it? I like the step-by-step instructions plus screenshots of the
results. And it uses the WAL.

My impression is that this is a user-friendly way for novices to backup
their databases hopefully without having the convoluted messes that I'm
currently having editing postgresql.conf, etc. Is that correct?


> Kind regards,
>
>
> On 2/21/20 12:18 PM, mimble9@danwin1210.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
>
>
>
>
>