Stack Overflow
Stack Overflow

Questions tagged [pg-dump]

pg_dump is a utility for backing up a PostgreSQL database.

656 questions
14
votes
2 answers

Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database

I am taking the dump of postgres database using "pg_dump database_name > backup.sql". Later on I am doing some modifications in the original database(database_name) and then I am restoring the data from the backup file(backup.sql). But the result is…
SonamGupta
  • 1,727
  • 2
  • 10
  • 14
14
votes
1 answer

Can pg_dump be instructed to create tables with "IF NOT EXISTS"?

As per the title. I've looked at the man page and it doesn't seem like there is any command-line argument to include the "IF NOT EXISTS" clause in the "CREATE TABLE" statements.
magnus
  • 4,031
  • 7
  • 26
  • 48
14
votes
2 answers

Postgres: Best way to move data from public schema of one DB to new schema of another DB

I am new to Postgres and just discovered that I cannot access data of different databases in one SQL query. And also learned the concept of schema in Postgres. Now, I have two databases db1 and db2 Both have tables with same name in their public…
Mandeep Singh
  • 7,674
  • 19
  • 62
  • 104
13
votes
4 answers

Comparing two postgres dump files

How to compare postgres dump files? I have two dump files, dump1 and dump2 . And I want to compare these two dump files. Any help will be appreciated.. Thank you
Rohita Khatiwada
  • 2,835
  • 9
  • 40
  • 52
13
votes
2 answers

PgAdmin 4: Terminate database backup process

I am attempting to back up a PostgreSQL database from pgAdmin 4, but a pg_dump backup process is already running, and I am unable to stop it. I've restarted the postgres server, restarted the server OS, and restarted pgAdmin, but the pg_dump backup…
Michael
  • 251
  • 1
  • 2
  • 13
13
votes
4 answers

"permission denied" for pg_dump output file

i used below command to backup my database sudo -u user_name pg_dump dbName -f /home ..../someWhere/db.sql but it gives me this : pg_dump: [archiver] could not open output file "/home ..../someWhere/db.sql": Permission denied after googling this…
Mostafa Jamareh
  • 1,389
  • 4
  • 22
  • 54
12
votes
5 answers

Use pg_restore to restore from a newer version of PostgreSQL

I have a (production) DB server running PostgreSQL v9.0 and a development machine running PostgreSQL v8.4. I would like to take a dump of the production DB and use it on the development machine. I cannot upgrade the postgres on the dev machine. On…
Phrogz
  • 296,393
  • 112
  • 651
  • 745
12
votes
5 answers

pg_dump windows command prompt invalid command

trying to using pg_dump to backup a postgres db i connected through the command prompt and here is my command following this tutorial http://www.postgresqltutorial.com/postgresql-backup-database/ pg_dump -U postgres -W -F t lucz_2017 >…
ziggy
  • 1,488
  • 5
  • 23
  • 51
12
votes
4 answers

pg_dump & pg_restore password using python module subprocess

Problem: Use the PSQL pg_dump and pg_restore in a Python script and using the subprocess module. Background: I am using the following python 2.7 script from the localhost (i.e. Ubuntu 14.04.5 LTS) to create a backup of a table in a PSQL server (i.e.…
Dimitris
  • 485
  • 1
  • 4
  • 16
12
votes
2 answers

Problems with $libdir on PostgreSQL

In short, my question is "why doesn't $libdir work on my PSQL installation." CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d AS '$libdir/liblwgeom', 'BOX2DFLOAT4_in' LANGUAGE c IMMUTABLE STRICT; yields an error could not access file…
Joe Germuska
  • 1,638
  • 1
  • 18
  • 34
11
votes
3 answers

Unable to restore psql database from pg_dump with a different username

I need to dump a postgres database from computer1 with postgres username1 and then restore it on computer2 with postgres username2. I keep running into the error that looks like the backup file wants to use username1: When I run this on computer2:…
richardsonae
  • 1,961
  • 2
  • 14
  • 18
11
votes
1 answer

Error in creating a database dump on PostgreSQL

When trying to make a postgreSQL database dump we got the following error and the process stops immediately. Command used: openbravo@master.akluck.com:~ 07/26 11:48:11> pg_dump -U tad -h localhost -p 5932 -F c -b -v -f…
Rinsad Ahmed
  • 1,877
  • 1
  • 10
  • 28
11
votes
3 answers

How do I take a postgres data backup on Hasura

I want to take a pg_dump of my data from HasuraDB to my local machine. What is the recommended way to do this ?
Jackspicer
  • 1,787
  • 2
  • 13
  • 21
10
votes
1 answer

pg_dump without setting search_path

When I use pg_dump to export schema from a database, it adds the following line at the beginning: SELECT pg_catalog.set_config('search_path', '', false); Is it possible set an option where pg_dump will not add this line? It is causing issues later…
Shahidh
  • 2,472
  • 1
  • 18
  • 18
10
votes
1 answer

Postgres pg_dump effect on production DB

Is it possible to query how many multiversioned rows are there in a db ? We want to measure the impact of pg_dump on a production database, and to suspend it in case of need: is it creating too many multiversioned rows? Thanks in advance
artejera
  • 1,346
  • 1
  • 11
  • 19
1 2
3
…
43 44