The pg-dump tag has no usage guidance.
1
vote
0answers
15 views
pg_dump and pg_dumpall tools backup create zero size backup file from linux putty terminal
I have installed postgres database version 9.6, 10 on OS Centos 7 64 bit. I'm trying to take a backup with this command:
-bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/...
0
votes
0answers
12 views
postgres 9.6 restoring a server in an other one
I made a pg_dumpall of a 9.6 postgres server in order to restore it (for the first time) completely on another computer. On this second computer, postgres 9.6 is also installed with old versions of ...
3
votes
2answers
1k views
Importing large SQL dump with millions of INSERT statements
I need to import a big .sql file (8.1GB when unpacked) into PostgreSQL. I tried to use \i /path/to/file.sql but it is way too slow.
How does one speed up the import? I need to import this data weekly....
0
votes
0answers
20 views
PG10: Not able to take backup
I have only one database PG10 is installed via EDB GUI installer on Ubuntu 16.04.3 LTS, Now I'm trying to take backup of my database and it is giving me an error, which I can't understand.
Command:
...
0
votes
0answers
113 views
Unable to access LOB stream after importing a data-only dump from some tables
We've been working on an application for a long time and after all the tests and adding necessary features, we decided to change the phase from testbed to production.
There were tables we needed the ...
0
votes
1answer
144 views
How to disable foreign key constraints in Postgres dump file?
I used pg_dump -U postgres -p 65432 --table=tableName --data-only --column-inserts DBName>fileName.sql to dump one particular table's rows. Now I need to insert them to another database but I get ...
1
vote
0answers
22 views
Dump limit in PostgreSQL
I am trying to dump a database which has a size of just over 100 GiB. I am using the following command (with pv to give a progress bar to observe where this problem occurs):
pg_dump -h <host> -...
0
votes
1answer
29 views
Change pg_dump directory output to table name instead of object number
pg_dump -F d will result in multiple <number>.dat.gz files which represent the object number in the database.
I wish pg_dump create those files in the format <table name>.dat.gz instead so ...
1
vote
0answers
104 views
Postgres pg_dump copy table to another database isn't copying columns
Goal: copy a single table including all columns from one database to another using psql on the command line, avoiding an issue with XML from QGIS layer styles.
I am running the following on the ...
2
votes
1answer
52 views
PG_DUMP on Replica DB server Error
I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it.
I need to run ...
0
votes
1answer
35 views
How do I copy a DB dump file to a remote computer? [closed]
I want to create a copy of my database from one server to another (my laptop to a server for example). Ive been looking online about how to do it and found this article:http://www.postgresqltutorial....
1
vote
0answers
90 views
Postgres pg_dump restore performance. Is using pg_dump to back up large databases an acceptable solution
Folks,
I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs.
My one concern is restoring Postgres from a dump. I believe that the ...
0
votes
1answer
368 views
out of shared memory error when doing pg_dump
I have a postgres database with 3 schemas and around 1200 tables. When I try to take a schema only pg_dump of it using the command, pg_dump -h localhost -p 5432 -U postgres -d my_test_db -s, I ...
0
votes
0answers
22 views
Postgresql: Restored dump into another database with another user, results in permission denied
I'm using postgresql on two machines, and I regularly create a dump on the production server and restore it on my development computer. Usernames are identical. The dump / restore porcess works in ...
0
votes
1answer
67 views
Schema Dump Without User Mapping Passwords
Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings?
For example, I would like items such as the following ...
0
votes
0answers
17 views
Rename a schema that contains an extension used by another schema
How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)?
I'm trying to temporarily rename a schema "dest" to "public" in ...
0
votes
1answer
32 views
Small databases, large backup files
We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point.
My database was 900MB, then I tried clearing it by ...
0
votes
1answer
189 views
pg_restore: [archiver] could not find entry for ID 12
I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error:
pg_restore: [archiver] could not find entry for ID 12
Exactly the same happens if ...
1
vote
0answers
29 views
Replace oldSchema with newSchema in functions
I exported a database called oldDatabase using pg_dump in directory format. This database contains a schema, tables and functions owned by oldRole. These functions contain SQL statements like
...
0
votes
1answer
2k views
pg_restore ERROR: “Relation does not exist” and creating new database
I have made a backup of my specific tables that I want to restore into a new database using:
call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public....
1
vote
0answers
26 views
Minimum permissions for postgres server dump
Sql request to create a user with the minimum rights necessary for the full backup of the server postgres (without data)
0
votes
0answers
172 views
Backup size is larger than the database size
I am taking a PostgreSQL database backup by using pg_dump method. My database size is 25 GB, but after taking the backup, the backup size is showing as 52 GB. I am totally confused why this is ...
2
votes
1answer
83 views
how to create ambari database backup without enter password
We configure the file .pgpass as follows in order to create database backups without entering a password:
-bash-4.2$ more .pgpass
localhost:5432:postgres:ambari:bigdata
-bash-4.2$ pwd
/var/lib/pgsql
-...
1
vote
2answers
854 views
pg_restore terminates with “out of memory” error
Trying to restore a 45mb pg_dump file but it keeps failing with an "out of memory" error.
Content of my postgresql-10-main.log:
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development LOG:
...
0
votes
1answer
285 views
pg_dump issue while dumping single schema
pg_dump does refuse to dump a given schema in my DB.
pg_dump -d <dbname> -s -n dm_geo -f <filename>
Result
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.5.4
-- ...
0
votes
1answer
123 views
how to prevent multiple pg_dump to execute at the same time
I am exposing a REST API to backup a Postgres DB. I have few questions related to that. I have seen most of these questions floating around google but could not find any suitable answer
Is there any ...
0
votes
1answer
467 views
How do I limit the resources that pg_dump uses?
Is it possible to limit the system resources used up by pg_dump?
I have a fairly resource intensive server that uses a PostgreSQL database. and it is running on FreeBSD 10.2. The server has 8 GB of ...
0
votes
1answer
246 views
Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS
I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1.
The restore seems successful, but the output gave me 27 ...
0
votes
1answer
980 views
pg_dump permission denied for sequence
I have a strange issue with dumping post-data section.
Dump command is:
pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump
...
1
vote
1answer
78 views
How do I get the GRANT statements for a specified schema?
Is there a way to generate the GRANT statements that have been run on a schema in PostgreSQL, for example if myUser has been granted usage on mySchema the script would generate
GRANT USAGE ON SCHEMA ...
2
votes
0answers
290 views
pg_restore error: Extensions installed out of sequence
I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command:
$ pg_dump -c -U postgres -Fc -f test.dump testdb
When I ...
7
votes
4answers
1k views
How do I prevent changes to my 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
vote
1answer
46 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?
2
votes
2answers
88 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 ...
1
vote
0answers
305 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/...
1
vote
2answers
678 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 ...
3
votes
1answer
484 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>
...
1
vote
2answers
279 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.
...
3
votes
2answers
154 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 ...
2
votes
1answer
418 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 ...
1
vote
1answer
2k 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 ...
2
votes
0answers
204 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 ...
3
votes
1answer
51 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 ...
8
votes
1answer
5k 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 ...
2
votes
1answer
239 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 ...
2
votes
1answer
3k 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 ...
5
votes
2answers
708 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 ...
4
votes
1answer
648 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?
2
votes
0answers
447 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 ...
3
votes
1answer
422 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 ...
