Re: [Bacula-users] PostgreSQL: WAL and log management
Thank you. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On 11/26/2018 11:40 AM, George via Bacula-users wrote: > Just one more question: when automating the backup of > all postgresql databases, should I also backup > template1? I have found that template0 cannot be > backed up due to how postgresql works but couldn't > find similar info about template1. I don't bother with its built-in databases: IME reinstalling postgres (which restores them) is as fast or faster than restoring from backup. If you're after high availability-level of backup, set up another server and run streaming replication to it. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
Thanks a lot for your help Dmitri. I decided to start from scratch for the sake of exploring how this would work without repeating many times the same things. So I: 1. Made a backup of /var/lib/pgsql 2. Stopped service and uninstalled postgresql 3. Removed /var/lib/pgsql 4. Reinstalled postgresql 5. Applied the conf changes discussed here 6. Imported the SQL data for bacula Right now data/pg_wal is 81M (5x16M) files in it. And data/base is 126M of which 105M seem to belong to bacula database. Just one more question: when automating the backup of all postgresql databases, should I also backup template1? I have found that template0 cannot be backed up due to how postgresql works but couldn't find similar info about template1. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Sun, 25 Nov 2018 21:16:03 +0200 George via Bacula-users wrote: > The WAL files remain though. Should I delete them > manually? Postgres will fail to start if they're actually used. I think there are some functions to figure out which ones are used but I suppose you could just move them to e.g. /var/tmp and tehn move them back one by one starting from newest until it starts. > I suppose it may be due to the > multiple imports and table deletions. But it still > looks too much considering that the total .sql file is > less than 70MB. Not really, I know that for e.g. deletions, a WAL stores the entire old row, and the entire new row. You could run a bunch of deletes and inserts and make it grow big, that's how yours could get that way. The issue though is that posgres is largely self-tuning, and normally that would gradually go down to max_wal_size. -- Dmitri Maziuk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
Thanks for explaining. So I have set: wal_level = minimal max_wal_senders = 0 and restarted postgresql successfully. The WAL files remain though. Should I delete them manually? As for this: > FWIW I left my few postgres-10 instances at the > defaults, except for one that is actually > replicated, they all seem to be honor the > max_val_size, and the ones that are mostly idle have > only 3-4 16MB files in pg_wal. So I don't know why > yours is using too much space in pg_wal: it could > grow over max_val_size when you run a lot of > transactions, but then it should come down > gradually. Checkpoint, specifically, should trigger > that. I don't know either. I suppose it may be due to the multiple imports and table deletions. But it still looks too much considering that the total .sql file is less than 70MB. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Sat, 24 Nov 2018 21:59:41 +0200 George via Bacula-users wrote: > On Sat, 24 Nov 2018 11:39:37 -0600 Dmitri Maziuk via > Bacula-users wrote: > > > So set > > max_wal_senders = 0 > > as well. Now looking at the fine manual I see they > > changed the default from 9.x, it used to be 0. > Could you please explain why this is what I should do? You can replicate your database to other postgres servers. It is done by sending WALs. If you are not doing that, you don't need WAL senders. Replication can create a backlog of WAL files. Those files if what you're trying to get rid of. FWIW I left my few postgres-10 instances at the defaults, except for one that is actually replicated, they all seem to be honor the max_val_size, and the ones that are mostly idle have only 3-4 16MB files in pg_wal. So I don't know why yours is using too much space in pg_wal: it could grow over max_val_size when you run a lot of transactions, but then it should come down gradually. Checkpoint, specifically, should trigger that. -- Dmitri Maziuk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Sat, 24 Nov 2018 11:39:37 -0600 Dmitri Maziuk via Bacula-users wrote: > So set > max_wal_senders = 0 > as well. Now looking at the fine manual I see they > changed the default from 9.x, it used to be 0. I read in the manual: "The value 0 means replication is disabled. [...] Abrupt streaming client disconnection might cause an orphaned connection slot until a timeout is reached, so this parameter should be set slightly higher than the maximum number of expected clients so disconnected clients can immediately reconnect." Could you please explain why this is what I should do? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Sat, 24 Nov 2018 01:36:12 +0200 George via Bacula-users wrote: > > wal_level = minimal > > and restart. > > Trying this results in (journalclt excerpt): > > Nov 24 01:11:05 pc postgresql-script[6374]: 2018-11-24 01:11:05.565 EET > [6383]FATAL: WAL streaming (max_wal_senders > 0) requires wal_level > "replica" or "logical" So set max_wal_senders = 0 as well. Now looking at the fine manual I see they changed the default from 9.x, it used to be 0. -- Dmitri Maziuk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Fri, 23 Nov 2018 15:54:38 -0600 Dimitri Maziuk via Bacula-users wrote: > If it still hasn't gone down, check your > postgresql.conf for > > #wal_level = replica# minimal, > replica, or logical # (change requires restart) > > If the above is what you have, you could change it to > wal_level = minimal > and restart. Trying this results in (journalclt excerpt): Nov 24 01:11:05 pc postgresql-script[6374]: 2018-11-24 01:11:05.565 EET [6383]FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "replica" or "logical" Nov 24 01:11:05 pc postgresql-script[6374]: pg_ctl: could not start server Nov 24 01:11:05 pc postgresql-script[6374]: Examine the log output. Nov 24 01:11:05 pc systemd[1]: postgresql.service: Control process exited, code=exited status=1 Nov 24 01:11:05 pc systemd[1]: Failed to start PostgreSQL database server. So I commented it back and restarted. > FWIW I just checked another of out postgresql-10 > hosts, this one does a lot of truncates and loaded > on a 10^6+ rows DB, it has #max_wal_size = 1GB > and ~999.5MB in pg_wal when idle. Thanks for the additional info. Right now in pg_wal I have 769MB with 47x16MB files from 19.Nov and 1x16MB from this exact moment. FWIW on this system there isn't any pgsql activity as the only database is that of bacula (and it is still empty as I was waiting for your reply). I read that: https://www.postgresql.org/docs/10/runtime-config-wal.html#GUC-MAX-WAL-SIZE After re-running a few times the scripts for export/import from/to mysql/psql the situation is: # du -hs ~/data/pg_wal/ 769M/var/lib/pgsql/data/pg_wal/ with 11x16MB from this moment and the others from 19.Nov. So obviously some WAL rotation does occur oldest files are replaced by new ones which keeps the overall WAL data the same. I guess this answers my main concern about WAL data (not) filling up the partition. Thank you. -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On 11/20/2018 06:32 PM, George Anchev via Bacula-users wrote: > $ du -hs pg_wal/ > 769Mpg_wal/ > $ psql -U postgres -d postgres -c checkpoint > CHECKPOINT > $ du -hs pg_wal/ > 769Mpg_wal/ If it still hasn't gone down, check your postgresql.conf for #wal_level = replica# minimal, replica, or logical # (change requires restart) If the above is what you have, you could change it to wal_level = minimal and restart. FWIW I just checked another of out postgresql-10 hosts, this one does a lot of truncates and loaded on a 10^6+ rows DB, it has #max_wal_size = 1GB and ~999.5MB in pg_wal when idle. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Tue, 20 Nov 2018 17:47:49 -0600 Dimitri Maziuk via Bacula-users wrote: > Check that wal archive_mode is off in > postgresql.conf and also all replication-related > stuff. grep -iE "replic|archive_mode" postgresql.conf shows only commented lines. Need I change anything? > Try `psql -U postgres -d postgres -c checkpoint` and > see if the files start disappearing after that. They don't: $ du -hs pg_wal/ 769Mpg_wal/ $ psql -U postgres -d postgres -c checkpoint CHECKPOINT $ du -hs pg_wal/ 769Mpg_wal/ -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] PostgreSQL: WAL and log management
PS https://www.postgresql.org/docs/10/sql-checkpoint.html -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On 11/20/2018 04:56 PM, George Anchev via Bacula-users wrote: > On the link I read wal_keep_segments "Specifies the > *minimum* number of past log file segments kept in the > pg_wal directory". Isn't there a setting which > controls the *maximum* number/volume/retention of those > files? (like in a log rotation) Not that I know of. They keep transactions, so they're not necessarily correspond to database size. Normally they get auto-recycled unless either a) archiving is on, but archive command keeps failing, or b) replication is on but the stand-by(s) keep failing to fetch them. Check that wal archive_mode is off in postgresql.conf and also all replication-related stuff. Try `psql -U postgres -d postgres -c checkpoint` and see if the files start disappearing after that. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Tue, 20 Nov 2018 16:34:58 -0600 Dimitri Maziuk via Bacula-users wrote: > I've 320MB on a server that replicates a database of > a few million rows, and ~100MB on our bacula server > w/ a year's worth of backups. What do you call "too > big"? As shared in the first message: Right now, even after drop_bacula_tables and no other databases (except what psql has as default after installation) I have: $ du -hs /var/lib/pgsql/data/pg_wal/ 769M/var/lib/pgsql/data/pg_wal/ That is for an empty bacula database. Those WAL files seem to be something like a "history" for what has been done during my tests: dropping tables, importing catalog, dropping again etc. The actual data which was used during those imports and drops (also shared in my first message): # du -hs bacula-backup.sql 63M bacula-backup.sql > There is wal_keep_segments: > https://www.postgresql.org/docs/10/runtime-config-replication.html > but if replication is not on, it's normally only a > handful of 16MB files in there, like 6 or so. Right now there are 52 files like those. I am not creating a super big enterprise system, so replication is not what I am looking for. On the link I read wal_keep_segments "Specifies the *minimum* number of past log file segments kept in the pg_wal directory". Isn't there a setting which controls the *maximum* number/volume/retention of those files? (like in a log rotation) -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On 11/20/2018 03:49 PM, George Anchev via Bacula-users wrote: > On Mon, 19 Nov 2018 17:16:52 -0600 Dimitri Maziuk via > Bacula-users wrote: > >> https://www.postgresql.org/docs/10/routine-vacuuming.html > > Good info. Thanks. > >> WAL: don't worry about it unless you want to archive >> them or replicate your database. > > But the pg_wal directory is way too big and I have no > idea how to decrease it (without damaging anything). > Hence the whole question: how to manage that? Is there > a way to (auto)vacuum this too? I've 320MB on a server that replicates a database of a few million rows, and ~100MB on our bacula server w/ a year's worth of backups. What do you call "too big"? There is wal_keep_segments: https://www.postgresql.org/docs/10/runtime-config-replication.html but if replication is not on, it's normally only a handful of 16MB files in there, like 6 or so. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On Mon, 19 Nov 2018 17:16:52 -0600 Dimitri Maziuk via Bacula-users wrote: > https://www.postgresql.org/docs/10/routine-vacuuming.html Good info. Thanks. > WAL: don't worry about it unless you want to archive > them or replicate your database. But the pg_wal directory is way too big and I have no idea how to decrease it (without damaging anything). Hence the whole question: how to manage that? Is there a way to (auto)vacuum this too? -- George ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] PostgreSQL: WAL and log management
On 11/19/2018 04:12 PM, George Anchev via Bacula-users wrote: > 1. Am I doing everything correctly? https://www.postgresql.org/docs/10/routine-vacuuming.html > 2. How to manage WAL and logs so that old unnecessary > data doesn't fill up disk (and backup tape) space? Logs: https://www.postgresql.org/docs/10/logfile-maintenance.html "Stock" postgresql.conf rotates them by day of the week in data/pg_log but check your postgresql.conf. WAL: don't worry about it unless you want to archive them or replicate your database. > 3. Should I backup WAL, logs or the > whole /var/lib/pgsql? Or should I rather look for a > script which backs up each DB in a separate file > (currently I have use such script for mysql). Bacula's backup_catalog should do it for bacula. For other databases you could e.g. run pg_dump from a cron job. If you're after high availability you could set up streaming replication to another postgres instance, that's where some WAL-related stuff comes in. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] PostgreSQL: WAL and log management
Hi,
So... I tested the whole process of migrating MariaDB
database to PostgreSQL. But psql seems a different
beast. What is confusing for me:
After several runs of {drop|make|grant}_bacula_tables
scripts for the sake of cleaning up and starting from
scratch while testing the process, I notice that even
after a drop of tables, psql data keeps piling up:
$ whoami
postgres
$ /opt/bacula/scripts/drop_bacula_tables
$ psql -l
List of databases
Name| Owner | Encoding | Collate |Ctype| Access privileges
---+--+---+-+-+---
bacula| bacula | SQL_ASCII | C | C |
postgres | postgres | UTF8 | C | en_US.UTF-8 |
template0 | postgres | UTF8 | C | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | C | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)
$ du -hs /var/lib/pgsql/
928M/var/lib/pgsql/
$ du -hs /var/lib/pgsql/data/
802M/var/lib/pgsql/data/
$ du -hs /var/lib/pgsql/data/pg_wal/
769M/var/lib/pgsql/data/pg_wal/
For comparison, the actual data (which was imported
and then dropped a few times):
# du -hs /var/lib/mysql/bacula/
84M /var/lib/mysql/bacula/
# du -hs bacula-backup.sql
63M bacula-backup.sql
Since the majority of the data is in pg_wall after
searching the web I read about WAL - something which I
haven't "met" in MariaDB. Unfortunately even after
reading the documentation and various articles all I
got is a head ache, so it is still not clear to me how
to manage this data which piles up more and more, even
without the actual data being re-imported as a final
step. I am worried that my /var may easily fill up
after I switch Bacula production to psql.
I also notice that psql keeps logs
in /var/lib/pgsql/data/log and a new log is created on
each restart of the service. Searching for how to
manage that I found this info:
https://serverfault.com/a/327913
But for openSUSE "There is no need to run a syslog
based service anymore, as all system events are
written in the journal.":
https://doc.opensuse.org/documentation/leap/reference/html/book.opensuse.reference/cha.journalctl.html
So what I did was to set in postgresql.conf:
log_destination = 'stderr' # it was that way
log_truncate_on_rotation = on
log_rotation_age = 1d
output log_rotation_size = 0
My questions are:
1. Am I doing everything correctly?
2. How to manage WAL and logs so that old unnecessary
data doesn't fill up disk (and backup tape) space?
3. Should I backup WAL, logs or the
whole /var/lib/pgsql? Or should I rather look for a
script which backs up each DB in a separate file
(currently I have use such script for mysql).
Any additional clarifications are very welcome.
--
George
P. S. I understand this is not 100% Bacula question but
rather a mix of Bacula+PostgreSQL but as it is quite
specific and related to a migration, I hope it is OK
to ask here.
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
