Questions tagged [pg-dump]
Use this tag for questions concerning pg_dump, the PostgreSQL tool for logical backup (also known as database export or database dump). Please also use the [postgresql] tag.
240
questions
10
votes
4
answers
5k
views
How do I prevent changes to my PostgreSQL database?
My steps:
run the final pg_dumpall on a server prod-server-old
Shut down prod-server-old for ever.
copy the output to a different server (prod-server-new)
Restore the db there.
All traffic goes to ...
- 1,521
1
vote
1
answer
139
views
How to avoid modifications during pg_dumpall [duplicate]
I want to do a final pg_dumpall on a server, then copy the output to a different server and restore the db there.
How can I avoid modification to the database during pg_dumpall?
- 1,521
3
votes
2
answers
1k
views
Merging multple pg_dumps with columns using auto key
I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables ...
- 131
1
vote
0
answers
3k
views
pg_restore on OS X
I'm running this command to export a database:
sudo PGUSER="postgres" PGPASSWORD="D" /Library/PostgreSQL/9.5/pgAdmin3.app/Contents/SharedSupport/pg_dump --format=c okay_db_3 > '/Volumes/1TB/...
- 11
1
vote
2
answers
5k
views
Data missing in PostgreSQL dump
I'm trying to verify that I am actually getting data in my pg_dump from a PostgreSQL 9.3 server. I'm specifically running the following to get a dump of my Foreman database:
pg_dump -Fc foreman -U ...
- 51
11
votes
1
answer
5k
views
how to dump a case-sensitive table within a specific schema
I'm trying to dump a case-sensitive table named app_auth.User by executing the following command
pg_dump --schema=app_auth -t '"User"' -U em3local -h 127.0.0.1 -Fc -a -f <path> <dbname>
...
- 245
3
votes
2
answers
5k
views
pg_dump: Show GRANT Statements only
I executed this manually:
GRANT SELECT ON TABLE my_table TO some_user;
How can I dump this statement again?
I looked at pg_dumpd but only found a way to do a data-only dump or a schema-only dump.
...
- 1,521
3
votes
2
answers
1k
views
Postgres dumping specific non-table objects
I find myself needing to migrate a specific application from one host to another. That application touches a subset of tables within a certain PostgreSQL database; in order to migrate properly, I need ...
- 133
4
votes
1
answer
2k
views
pg_dump of table and its transitive dependencies
Sometimes I'd like to export just a single table and its dependencies, so I can do some experiments with it on a test database. Is there a way to do that, other than dumping the full db schema with ...
- 143
7
votes
1
answer
8k
views
PostgreSQL dump and restore
I can't seem to dump/restore on Windows with my database. Lines used and error messages:
pg_dump -h localhost -U postgres --format=c -O -d ue > latest.dump
pg_restore -h localhost -U postgres -d ...
- 273
1
vote
0
answers
1k
views
PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump
(Version 9.3)
Asking this after searching around without any solution:
I create a simple materialized view (in practice, I have a few of them).
I call pg_dump to backup the database.
While pg_dump ...
- 121
3
votes
2
answers
534
views
Getting Postgres CREATE TABLE statements
I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
- 416
34
votes
4
answers
72k
views
postgres - pg_dump and pg_restore without roles
I'm trying to restore a dump without having the appropriate roles on the receiving database.
As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or ...
- 667
2
votes
1
answer
1k
views
Idempotent PostgreSQL schema dump that can be applied to an existing DB
I'm looking for a way to replicate a PostgreSQL schema to a database which may already have some of it defined. I wonder if there's a simple way to produce DDL that is idempotent (i.e. that can be ...
- 173
3
votes
1
answer
12k
views
pg_restore: [custom archiver] could not read from input file: end of file
Not going into details how that happened, all started with a corrupt DB. It was OK on the file-system level, but trying to:
select count(*) from mytable
resulted in the following:
ERROR: could ...
- 31
5
votes
2
answers
6k
views
How to anonymize pg_dump output before it leaves server?
For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the ...
- 1,433
9
votes
2
answers
7k
views
How to exclude tables when using pg_dumpall with postgresql?
If use pg_dump, it has a --exclude-table-data option to ignore special table.
But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
- 343
2
votes
0
answers
2k
views
How to transfer table from one database to another in postgresql
pg_dump -h host_ip -U postgres -d source_db -t aa_nqrgs.act_26022017 |
psql -h destination_host_ip -U postgres -d destination_db
I have tried this command to transfer a table from one server to ...
- 61
7
votes
4
answers
5k
views
How to run pg_dump so that it doesn't slow down other connections?
I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size:
pg_dump -Fc dbname > file
The problem I am having is that while pg_dump runs, which takes several hours, all ...
- 71
-1
votes
2
answers
2k
views
What is the key to pg_restore?
We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore.
We are ...
- 9
6
votes
1
answer
2k
views
When pg_restore honours --dbname option
I am trying to restore a binary pg_dump backup under a different database name that was in the original dump. Despite giving --dbname command line argument, pg_restore tries to restore everything ...
- 299
3
votes
0
answers
2k
views
How do I generate a Postgres dump with the column names in my INSERT statement?
I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box. I’m using this command to export
pg_dump -U username --format plain --section data --...
- 693
9
votes
2
answers
15k
views
Differences between .dump and .sql in pg_dump PostgreSQL
I'm trying to take a backup of schema using pg_dump like,
$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump
It create the backup, But I can't restore it using pg_restore. It give ...
- 513
26
votes
6
answers
142k
views
Delete all data in Postgres database
I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a ...
- 451
0
votes
1
answer
217
views
change MySQL commands to PostgreSQL in a bash script
I have a bash script to backup MySQL databases. I have pasted the relevant part below (ignore the variables).
The 2 lines I think I need to change to make this work in PostgreSQL are;
DBS="$(mysql --...
- 207
1
vote
0
answers
1k
views
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology crashes server
I am moving to a new Windows server and upgrading PostgreSQL to 9.6 and PostGIS to 2.3 while I am at it (old versions were 9.2.4 and 2.0).
I used pg_dumpall to dump the entire server. Came to 318GB.
...
- 211
2
votes
0
answers
234
views
Can I calculate the number of locks required by pg_dump ahead of time?
I've got a multi-tenant app on postgres 9.5 DB with some 200+ schemas each with about 170 tables on. pg_dump operations have become a frustrating hit and hope exercise where I fiddle with ...
- 317
0
votes
1
answer
3k
views
AWS Postgres RDS dumping data into sql format
My team has hosted a django project in AWS with a Postgres RDS instance. What is the proper way to get export the database into SQL or pg_dump?
- 103
2
votes
1
answer
1k
views
postgres makes db dump without password
if i write on console at home (new machine, random IP):
/usr/bin/pg_dump --host xxxxxx --port xxxxxx --username "yyyy"
--no-password --format plain --create --clean --section pre-data --section data ...
- 161
6
votes
1
answer
1k
views
Postgresql: How to avoid encoding issues when copying a schema from one server to another?
I'm using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server:
$ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema....
- 471
35
votes
4
answers
49k
views
how to track progress of a large postgres dump
Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
- 453
7
votes
1
answer
8k
views
postgres backup for specific table with blob oid
I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 ...
- 785
0
votes
1
answer
1k
views
Cannot import database exported by phpPgAdmin
I have exported database with phpPgAdmin tool - format sql, structure and data.
When I try to import this sql using:
psql command \i filename.sql
After some time (file is 2GB) I get following ...
- 103
0
votes
1
answer
2k
views
Unable to backup postgresql database. pg_dump memory alloc error
After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished.
The very first thing I attempt to do is to ...
- 111
9
votes
1
answer
12k
views
PostgreSQL 9.3: Dump a database excluding the data of some tables
Consider a production database with a few dozens of normal-size tables, and a handful of huge tables.
I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, ...
- 11.7k
2
votes
1
answer
14k
views
How to convert postgresql dump file into mysql dump file
I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance
- 23
1
vote
1
answer
1k
views
pg_dump fails to create a backup when running from System User in Windows Task Scheduler
I am trying to run a pg_dump through a scheduled task in windows. I have two batch files. One creates the task. The other one runs the pg_dump. Even though the pg_dump works with the current user with ...
- 111
0
votes
1
answer
2k
views
"Unrecognized configuration parameter: qreplace_function" trying to backup a database
I am new to PostgreSQL and I am trying to backup a database with the pg_dump utility.
I am using this command:
/opt/PostgresPlus/9.4AS/bin/pg_dump -U (username) -f /opt/PostgresPlus/9.4AS//pgbackup/...
- 1
0
votes
1
answer
373
views
Postgres Data Dump problem
I am trying to dump this data into Postgres:
COPY address_types (game_id, device_type) FROM stdin;
1 Ps3
2 Xbox
3 Other
\.
But I always get this error:
[Err] ERROR: syntax error at or ...
6
votes
1
answer
5k
views
dumping database with "compressed data is corrupt" errors
I'm using pg_dump to dump a large (396 GB) PostgreSQL database:
pg_dump --clean --create mydatabase
After running for a day, a statement fails with ERROR: compressed data is corrupt and pg_dump ...
- 451
1
vote
1
answer
295
views
What determines whether `pg_dump` includes a varchar length?
I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns ...
- 885
1
vote
1
answer
803
views
Why can't I restore the DB from an SQL dump in PostgreSQL 9.4 (problem with dblink)?
I have created a dump of a DB using:
pg_dump db_name > db_dump.sql
and proceed to restore it with:
psql --single-transaction db_name < db_dump.sql
Prior to attempting to restore the DB, I ...
- 937
2
votes
1
answer
528
views
Replace content of only one table with new version (should disable foreign keys?)
I have 2 tables. 2nd has foreign keys to reference 1st.
I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and ...
- 445
6
votes
2
answers
4k
views
How to speedup pg_dump big database
I've got big database (about 900GB), and when I migrate it to new server dump speed is not more than 2MB/s (very slow). Link between servers is 10Gb/s.
The database contain 20 tables with different ...
- 61
4
votes
1
answer
1k
views
Output from `pg_dump --schema-only` has inconsistent order
I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers:
2896c2896
...
- 345
1
vote
2
answers
766
views
Why isn't pg_restore --create working?
I backed up a database called app_data, on a 9.3 server, with this basic command:
pg_dump -n public -F custom app_data > app_data.pg.dump
Then I try to restore it on another server (running 9.4) ...
- 111
2
votes
1
answer
15k
views
How do I load a data-only dump created with pg_dump to another database?
I have two PostgreSQL databases on my development server, one I use for development and another for production where I test my web applications. When I was using an earlier version of PostgreSQL I was ...
3
votes
2
answers
4k
views
pg_restore does not restore blobs
I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command:
pg_dump --format=c --...
- 5,002
15
votes
4
answers
40k
views
Restoring from pg_dump with foreign key constraints
In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:
ERROR: insert or update on table "channelproducts&...
- 279
6
votes
2
answers
11k
views
pg_restore not restoring sequences [closed]
I'm transferring all of the databases on my old postgres server to a new server. To do this I'm using pg_dumpall and then pg_restore. However, it appears that no sequences have been dumped. This is ...
- 373