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

Поиск
Список
Период
Сортировка
От mimble9@danwin1210.me
Тема Confused about how to enable backups (e.g. Write Ahead Log).
Дата
Msg-id 2e892aa39c48c34184ab1d97a750a089.squirrel@danielas3rtn54uwmofdo3x2bsdifr47huasnmbgqzfrec5ubupvtpid.onion
обсуждение исходный текст
Ответы Re: Confused about how to enable backups (e.g. Write Ahead Log).  (Stefan Fercot <stefan.fercot@dalibo.com>)
Re: Confused about how to enable backups (e.g. Write Ahead Log).  (Stephen Frost <sfrost@snowman.net>)
Список 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!






В списке pgsql-novice по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Outer Right Join?
Следующее
От: Stefan Fercot
Дата:
Сообщение: Re: Confused about how to enable backups (e.g. Write Ahead Log).