Questions tagged [pg-dumpall]
27 questions
0
votes
1 answer
AWS RDS Postgres error while taking the dump
When I try to take PostgresDump (AWS RDS) the following error I am getting:
ERROR: permission denied for relation dms_stats_detailed
pg_dump: error: query was: LOCK TABLE table_name IN ACCESS SHARE MODE
I am having admin permission though (with…
user8445210
0
votes
1 answer
How to append log output of pg_dump backup command PostgreSQL
I am using below command to append.
pg_dump -Fc -h hostname -W --dbname=dbanme -f dbname.sql --username 2>demo.log
I want to log into demo.log like below sample output.
pg_dump: reading user-defined tables
pg_dump: reading packages…
Adam Mulla
- 61
- 3
- 12
0
votes
1 answer
Is there a way to dump the whole postgres db using pg_dumpall from remote host by specifying certificates?
I have a kubernetes cluster and I have configured a statefulset with two replicas (lets say postgres-0 and postgres-1), one will be active and another will be standby for a perticular time. Now I want to run pg_dumpall from postgres-1 to connect to…
Soumyajit
- 329
- 1
- 5
- 16
0
votes
2 answers
Delete existing file after successful write
I am performing a daily database dump in a python script and I am looking for the most python efficient way to delete the current file only after the recent one was written successfully, i.e. delete the backup-12-11-2019.sql only after…
zamponotyropita
- 329
- 1
- 2
- 10
0
votes
1 answer
Server Backup in postgres
I have to take backup of a remote server and restore it. So, whats the best way to take backup of whole server?
After logging into the root server i have tried:-
[root@server-14 ~]# pg_dumpall > clus.bak
pg_dumpall: could not connect to database…
akhilesh kedarisetty
- 117
- 3
- 10
0
votes
0 answers
pg_dumpall | ssh username@server 'cat > alldb.sql'
Im trying to dump my local postgresql database to remote server but
pg_dumpall | ssh username@server 'cat > alldb.sql'
output
pg_dumpall: could not connect to database "template1": could not connect to server: No such file or directory
Is the…
jagtar
- 23
- 8
0
votes
1 answer
pg_dumpall > alldb.sql output bash: alldb.sql: Permission denied
Im trying to take all backup by running these as superuser i.e postgres but
pg_dumpall > alldb.sql
bash: alldb.sql: Permission denied
pg_dumpall works but give backup as text in terminal
made changes in pg_hba.conf
pg_hba.conf
jagtar
- 23
- 8
0
votes
1 answer
pg_dumpall without dropping the superuser
I have a postgres 9.6. I am using pg_dumpall with -c --if-exists options.
When I restore from this backup file as the superuser I am getting errors current user cannot be dropped when it tries to drop the role. And after that I am getting role…
Nuri Tasdemir
- 9,720
- 3
- 42
- 67
0
votes
1 answer
How to restore postgres only when there is no data
I want to restore postgres from the output of pg_dumpall. However I want the restore to fail if there is any data in postgres at the moment. Is there a way to check this? In other words I want my restore to work only in a completely empty db…
Nuri Tasdemir
- 9,720
- 3
- 42
- 67
0
votes
1 answer
How to run pg_dumpall in enterprisedb 9.6
Need help backing up a database in enterprisedb. The pg_dumpall command doesn't work. When I run the command, it says can't connect to the database. I connect to the database and run the command, it doesn't give any errors, but the file doesn't show…
Phil
- 1
- 1
0
votes
1 answer
pg_dumpall excluding some tables
I want to get a full backup of postgres 9.6. Including the users and permissions. However I want to exclude some tables. In pg_dump there is an option for excluding some tables (-T). However in pg_dumpall there are no such options.
Is there a way…
Nuri Tasdemir
- 9,720
- 3
- 42
- 67
0
votes
1 answer
Removing duplicate schema/tables from a postgres database that had a dump all restored to it
Today I accidentally restored a pg_dumpall file to the database that that dumpall was generated from. This restore has caused a ton of duplicates of certain ID's and such.
This is a complex production database and it hasn't corrupted the entire…
user275615
- 71
- 7