[Spacewalk-list] postgres DB backup question

[Paschedag, Robert]

This is how I backup my spacewalk db

# crontab -l -u postgres
# make backup of spacewalk db
0 22 * * *      /usr/bin/pg_dump --clean rhnschema 2>&1 1> /var/lib/pgsql/backups/spacewalk_rhnschema_backup_daily.sql

And this file gets backed up every night from backup software.

Robert


-----Ursprüngliche Nachricht-----
Von: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] Im Auftrag von Robert Paschedag
Gesendet: Montag, 26. Februar 2018 20:57
An: spacewalk-list at redhat.com; Nicole Beck <nskyrca at syr.edu>; 'spacewalk-list at redhat.com' <spacewalk-list at redhat.com>
Betreff: Re: [Spacewalk-list] postgres DB backup question

Am 26. Februar 2018 19:23:52 MEZ schrieb Nicole Beck <nskyrca at syr.edu>:
>Hello!
>I'm trying to backup my postgress DB using the Spacewalk install 
>documentation at 
>https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgith
>ub.com%2Fspacewalkproject%2Fspacewalk%2Fwiki%2FSpacewalkBackup&data=02%
>7C01%7CPaschedag.Netlution%40swr.de%7C39df6f57559f4e20ce7908d57d532d4b%
>7Cbcca095d88d442f88260cc216b81f62d%7C0%7C0%7C636552718590450390&sdata=e
>kCaKSqLjOPI1n7tcV9JKStZ8yjejFaB1R75AgFG5jw%3D&reserved=0, and it fails 
>with the errors below. If I try it without spacewalk running, it gives an error and creates an empty backup file. If I try it with spacewalk running, it creates a 3.9 GB backup file, but I can't restore it. Spacewalk is the only thing using this database. How do you all do your database backups?
>
>Errors when spacewalk not running(creates empty backup file):
>
>[root at spacewalk ~]# spacewalk-service stop Shutting down spacewalk 
>services...
>Redirecting to /bin/systemctl stop taskomatic.service
>Stopping cobblerd (via systemctl):                         [  OK  ]
>Redirecting to /bin/systemctl stop rhn-search.service Redirecting to 
>/bin/systemctl stop osa-dispatcher.service Redirecting to 
>/bin/systemctl stop httpd.service Redirecting to /bin/systemctl stop 
>tomcat.service Redirecting to /bin/systemctl stop jabberd.service 
>Redirecting to /bin/systemctl stop postgresql.service Done.
>[root at spacewalk ~]# su - postgres -c  'pg_dumpall > 
>/var/lib/pgsql/backups/full_postgres_backup-`date +%Y%m%d`.sql'
>pg_dumpall: could not connect to database "template1": could not 
>connect to server: No such file or directory
>        Is the server running locally and accepting  connections on 
>Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>[root at spacewalk ~]# ls -al /var/lib/pgsql/backups total 0
>drwx------ 2 postgres postgres  47 Feb 26 12:32 .
>drwx------ 6 postgres postgres 122 Nov  1 14:55 ..
>-rw-r--r-- 1 postgres postgres   0 Feb 26 12:32
>full_postgres_backup-20180226.sql
>
>
>Errors when spacewalk running
>
>[root at spacewalk ~]# spacewalk-service start Starting spacewalk 
>services...
>Redirecting to /bin/systemctl start
>Redirecting to /bin/systemctl start postgresql.service Redirecting to 
>/bin/systemctl start jabberd.service Redirecting to /bin/systemctl 
>start tomcat.service Waiting for tomcat to be ready ...
>Redirecting to /bin/systemctl start httpd.service Redirecting to 
>/bin/systemctl start osa-dispatcher.service Redirecting to 
>/bin/systemctl start rhn-search.service
>Starting cobblerd (via systemctl):                         [  OK  ]
>Redirecting to /bin/systemctl start taskomatic.service Done.
>[root at spacewalk ~]# su - postgres -c  'pg_dumpall > 
>/var/lib/pgsql/backups/full_postgres_backup-`date +%Y%m%d`.sql'
>pg_dump: [archiver (db)] connection to database "template1" failed:
>FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", 
>database "template1", SSL off
>pg_dumpall: pg_dump failed on database "template1", exiting 
>[root at spacewalk backups]# ls -al total 3910032
>drwx------ 2 postgres postgres         47 Feb 26 12:32 .
>drwx------ 6 postgres postgres        122 Nov  1 14:55 ..
>-rw-r--r-- 1 postgres postgres 4003869597 Feb 26 12:40 
>full_postgres_backup-20180226.sql
>
>But you can't restore per the github.com directions:
>
>[root at spacewalk backups]# su - postgres Last login: Mon Feb 26 12:38:18 
>EST 2018 on pts/0 -bash-4.2$ SPACEWALK_DB_NAME=spacewalkdb -bash-4.2$ 
>ls /var/lib/pgsql/backups full_postgres_backup-20180226.sql -bash-4.2$ 
>SPACEWALK_DB_BACKUPFILE=/var/lib/pgsql/backups/full_postgres_backup-201
>80226.sql -bash-4.2$ full_postgres_backup-20180226.sql^C
>-bash-4.2$ id
>uid=26(postgres) gid=26(postgres) groups=26(postgres) -bash-4.2$ dropdb 
>$SPACEWALK_DB_NAME
>dropdb: could not connect to database template1: could not connect to
>server: No such file or directory
>        Is the server running locally and accepting  connections on 
>Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
>
>Thanks!
>Nicole
>
>
>Nicole Beck
>Information Technology Analyst
>Information Technolgy Services - Core Infrustructure Services - Unix
>315.506.9744
>nskyrca at syr.edu<mailto:nskyrca at syr.edu>
>215 Machinery Hall, Syracuse, NY 13244
>syracuse.edu
>Syracuse University

The DB needs to be running so you can back it up. Use pg_dump.
--
sent from my mobile device

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.redhat.com%2Fmailman%2Flistinfo%2Fspacewalk-list&data=02%7C01%7CPaschedag.Netlution%40swr.de%7C39df6f57559f4e20ce7908d57d532d4b%7Cbcca095d88d442f88260cc216b81f62d%7C0%7C0%7C636552718590450390&sdata=PE1SxoRfr%2BZ3TGc34C5NsXGs57baRniPjHv2WYnzEDY%3D&reserved=0