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

Hi,

On 2/22/20 12:07 AM, mimble9@danwin1210.me wrote:
> 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

That's a problem. /test has to be writable by the postgres linux user.

-> "sudo -u postgres touch /test/some_file" should work.

Below commands should solve that :

chown postgres: /test
chmod 750 /test

Of course, /test isn't really suitable in long term to store backups. 
Don't let that stored locally, and give your directory a representative 
name like pgbackups or something like that.

Furthermore, if the PostgreSQL cluster has a specific name or meaning 
(like an application name), try not to store files in the main directory 
of a mount point. It would be safer to use something like 
/pgbackups/app-or-cluster-representative-name.

>> 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?
>
In fact, as Stephen explained, WAL "backup" (archives) isn't enough. 
Point-in-time recovery can be achieved by combining a file-system-level 
copy and WAL archiving. Then, re-apply WAL archives on top of the copy 
to restore the data to a specific target (time, transaction id,...).

pg_basebackup is one way to take a copy. Then indeed, you need to handle 
WAL archives and pg_basebackup copies retention,...

pgBackRest is a tool that will help you for that. It will provide a 
specific command for WAL archiving ( PostgreSQL archive_command) and 
commands to take "backups" (file-system-level copies). Those backups can 
be full, differential or even incremental. You'll still need to 
configure postgresql.conf to configure WAL archiving.

pgBackRest will also help with the "restore" command.

(And yes, I already used it a lot. I'm like a "super fan" : 
https://pgstef.github.io/pitr/)

>> 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