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
