Skip to main content
Share Your Experience: Take the 2024 Developer Survey
33 votes
Accepted

How to Install postgresql-client-11 on Ubuntu 18.04

Install it from PostgreSQL's own apt repository, adapted from these instructions. sudo apt-get install curl ca-certificates gnupg curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-...
jjanes's user avatar
  • 40.1k
24 votes

postgres - pg_dump and pg_restore without roles

Using PostgreSQL 12.4: $ pg_dump --help ... -O, --no-owner skip restoration of object ownership in plain-text format ... -x, --no-privileges do not ...
Duke's user avatar
  • 341
19 votes
Accepted

postgres - pg_dump and pg_restore without roles

The error occurs when pg_restore set the ACLs : you can use --no-acl to prevent GRANT commands. With the -Ft option in pg_dump, you can skip roles and ACLs only in pg_restore. You can also edit the ...
slardiere's user avatar
  • 636
18 votes

Restoring from pg_dump with foreign key constraints

You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work ...
achimh's user avatar
  • 288
15 votes

Differences between .dump and .sql in pg_dump PostgreSQL

The file extension means nothing. At all. It's just a part of a file name. If you want a custom-format dump for use with pg_restore use -Fc as an argument to pg_dump. pg_dump defaults to generating ...
Craig Ringer's user avatar
  • 56.6k
15 votes
Accepted

how to track progress of a large postgres dump

You can see a rough progress using the TOC list. First, get the TOC list of objects to be restored: pg_restore -l -f list.toc db.dump Then, you can see the TOC list line by line and compare the ...
MatheusOl's user avatar
  • 2,539
15 votes
Accepted

PostgreSQL 9.3: Dump a database excluding the data of some tables

I think you need to use --exclude-table-data=table option. From the docs: --exclude-table-data=table Do not dump data for any tables matching the table pattern. The pattern is interpreted according ...
ypercubeᵀᴹ's user avatar
15 votes
Accepted

Importing large SQL dump with millions of INSERT statements

This dump was dumped as individual statements (with pg_dump --inserts) INSERT INTO esa2010_codes VALUES (11002, 'Národn INSERT INTO esa2010_codes VALUES (11003, 'Nefina INSERT INTO esa2010_codes ...
Evan Carroll's user avatar
  • 63.5k
14 votes

Why postgresql pg_dump exports a view as a table?

Internally, a view is just a table with a rule, so this makes sense. See here: https://postgresql.org/docs/9.5/static/rules-views.html Views in PostgreSQL are implemented using the rule system. ...
Max Murphy's user avatar
12 votes

how to track progress of a large postgres dump

Valid for Unix/Linux environments: The Pipe Viewer (pv) utility can be used trace the backup progress. The pv animates your shell with details about the elapsed time and transferred bytes. Below is ...
R0B0T-B0BA's user avatar
10 votes
Accepted

pg_restore ERROR: "Relation does not exist" and creating new database

When --create and -d are used together, the argument to -d is not the name of the database to create, it's the name of an existing database to connect to run the CREATE DATABASE statement, because it'...
Daniel Vérité's user avatar
9 votes

Delete all data in Postgres database

pg_restore has a --clean flag (or possibly --create) which will auto delete data before running operations.. The Excellent Documentation should help you greatly... Just to clarify, in case it's ...
Joishi Bodio's user avatar
  • 3,388
9 votes
Accepted

how to dump a case-sensitive table within a specific schema

When you specifies the schema name to unload it does not mean that it will be default schema to search objects: nd@postgres=# create schema foobar; CREATE SCHEMA nd@postgres=# create table foobar.&...
Abelisto's user avatar
  • 1,559
8 votes
Accepted

PostgreSQL dump and restore

Apparently the problem was the > operator. Changing that to -f to specify the file for the dump, resolved the problem (using the plain format). I was writing a Powershell script which may have ...
Josh's user avatar
  • 273
8 votes
Accepted

pg_dump permission denied for sequence

Might be an AWS-RDS specific issue but it works if you explicitly specify the schema to be dumped. pg_dump -f lol.sql -Fc -v -n public -O -x --section=post-data -h 127.0.0.1 -p 9999 -U root db-name
ZeroMax's user avatar
  • 196
7 votes

pg_dump with ssl certificates

The following worked for me: pg_dump "port=<port> host=<host> user=<user> dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-ca" -f &...
Kasun's user avatar
  • 71
6 votes

How to make pg_dump skip extension?

You can do pg_dump ... | grep -v -E '^(CREATE\ EXTENSION|COMMENT\ ON)' >out.sql it's what i use to import to google cloud sql with postgres. edit: added 'start of line' caret to not exclude ...
Rene's user avatar
  • 61
6 votes
Accepted

pg_dump custom archive and create backup date

You can use pg_restore -l <custom_dump_file> and the output should start with something like the following: ; ; Archive created at 2019-02-13 22:59:59 UTC ; dbname: <database_name> ; ...
Colin 't Hart's user avatar
6 votes
Accepted

Database Size is Different after pg_restore (389 GB vs 229 GB)

Dump doesn’t account for dead tuples and only takes live tuples into account but dead tuples do account for space hence the space difference. The reason is dump being a logical one it will only ...
Raj Verma's user avatar
  • 186
6 votes
Accepted

How to prevent pg_dump from generating "SET default_table_access_method=heap;"?

No, there is no such way, because downgrading PostgreSQL is not supported. You'll have to manually edit the dump and remove lines such as that until the dump loads without error. There could be other, ...
Laurenz Albe's user avatar
5 votes

How to make pg_dump skip extension?

Use the -L flag with pg_restore after taking a dump in a custom file format. -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in ...
akahunahi's user avatar
  • 151
5 votes
Accepted

When pg_restore honours --dbname option

There are few command line options you need to consider. pg_restore -x --no-owner --dbname=mynewdb --exit-on-error -Fc database.bak -Fc: restore from a binary back up as its more flexible than ...
Mikko Ohtamaa's user avatar
5 votes

How to exclude tables when using pg_dumpall with postgresql?

The name pg_dumpall itself says “dump all”, which means you can't exclude anything. There is no such pg_cumpall option available. Use pg_dump instead.
Ashiq Ahamed's user avatar
5 votes

postgres - pg_dump and pg_restore without roles

The following commands export data without mixing it with role or privilege data so that you can import it to any role you specify. Also, you can make pg_dump compress the data to a tar file for you. ...
Siddharth Pant's user avatar
5 votes
Accepted

What is the correct way to make an exact copy of a database with pg_dump?

I'm not a PostgreSQL expert but I think their documentation is rather clear and I believe the command you're using is pretty much all you need (you might need to specify the password with a flag - not ...
J.D.'s user avatar
  • 37.8k
5 votes
Accepted

pg_restore: error: could not execute query: ERROR: option "locale" not recognized

the version of PostgreSQL itself I'm using on my local server, is reported as 12.7 But still it's pg_dump 13.x that created the dump with the LOCALE argument to CREATE DATABASE. /usr/bin/pg_dump as ...
Daniel Vérité's user avatar
4 votes

postgres makes db dump without password

ops, how stupid I am... i have used 1 time pgadmin and logged to my external postgress server it made in my home directory .pgpass file if i delete it - pg_dump doesn't work without password my ...
blackmoon's user avatar
  • 161
4 votes

Delete all data in Postgres database

SELECT 'TRUNCATE ' || input_table_name || ' CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name FROM information_schema.tables WHERE table_schema NOT IN ('...
Thirumal's user avatar
  • 2,448

Only top scored, non community-wiki answers of a minimum length are eligible