I wanted to move the bacula database from Postgresql 11 (PG11) to Postgresql 13 (PG13) in advance of an operating system upgrade from Debian 11.6 to Debian 12, and eventual upgrades to Postgresql (version current in Debian 12). Unfortunately, running dbcheck on the moved database provoked an 'out of memory' error from PG13.
My basic plan was: 1) Backup the bacula database from PG11. 2) Restore the bacula database to PG13. 3) Run dbcheck on the PG13 database. 4) If dbcheck results are satisfactory, shut down Bacula, change the port number Bacula uses to reach Postgres, and restart Bacula. Unfortunately, the dbcheck failed in the 'Checking for orphaned Filename entries' step, as shown: ... Checking for orphaned Filename entries. This may take some time! Query failed: SELECT Filename.FilenameId,File.FilenameId FROM Filename LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) WHERE File.FilenameId IS NULL LIMIT 300000: ERR=ERROR: out of memory DETAIL: Failed on request of size 32768 in memory context "HashTableContext". I originally copied the postgresql.conf from /etc/postgresql/11/main/ to /etc/postgresql/11/main/, and received an out of memory error from Postgres. Some searching in the bacula-users list archives found a thread starting 24 Feb 2023 reporting out of memory errors that were eventually resolved by applying the recommendations of the pgtune website at https://pgtune.leopard.in.ua/. So, I used the pgtune tool and received these values: checkpoint_completion_target = 0.9 default_statistics_target = 500 effective_cache_size = 1536MB effective_io_concurrency = 200 maintenance_work_mem = 256MB max_connections = 20 max_wal_size = 16GB min_wal_size = 4GB random_page_cost = 1.1 shared_buffers = 512MB wal_buffers = 16MB work_mem = 6553kB I applied these changes to postgresql.conf, and restarted PG13. I used echo "show all" | psql -p 5433 to confirm the changes had been applied. After making that change, dbcheck runs to completion under PG13, in about 1/2 the time required under PG11. FYI. Ken _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
