Hot answers tagged

31

You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using drop owned by adminuser If you then create the SQL dump including the create table statements (so without the --data-only option) everything should be fine. You can also remove the --column-inserts then, which will make the import a ...


22

So you look up other tables in a CHECK constraint. CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction in the manual: Currently, CHECK expressions cannot contain subqueries nor refer ...


21

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format. From pg_dump manpage: -F format, --format=format Selects the format of the output. format can be one of the following: p, plain Output a plain-text SQL script file (the default). A ...


18

This is a dump file produced by pg_dump -Fc. To create a text file containing SQL commands from it, use pg_restore. The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).


18

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-key add - sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' sudo apt-get update ...


16

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 catalog with --list if you need more details.


14

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 VALUES (12502, 'Národn INSERT INTO esa2010_codes VALUES (11001, 'Verejn INSERT INTO esa2010_codes VALUES (12602, 'Národn INSERT INTO esa2010_codes VALUES (12603, '...


12

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. In fact, there is essentially no difference between: CREATE VIEW myview AS SELECT * FROM mytab; compared against the two commands: CREATE TABLE myview (...


11

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 to the same rules as for -t. --exclude-table-data can be given more than once to exclude tables matching any of several patterns. This option is useful when you ...


11

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 output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in. It is just a rough estimate though. First because each item from the ...


10

For anyone looking for a workaround, limiting pg_restore to a specific schema has helped me get around this bug. See https://stackoverflow.com/a/11776053/11819


10

You must be running in a Security Group firewall problem. Go to your RDS Dashboard, select Instances and open the instance you want to connect to. Look for a line like this : Security Groups rds-launch-wizard (sg-3e9axxx) ( active ). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups ...


9

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 outside this script. But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at ...


9

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 SQL-format dumps for use with psql. See the manual for more details.


8

Not really. You have to remember that pg_dump command creates simple CREATE TABLE and INSERT statements etc. So effectively when running pg_restore you're just running CREATE and INSERT statements on the server and inserting the data would require a "INSERT INTO MATERIALIZED VIEW"-command. That wouldn't make sense as getting the data by a shortcut would also ...


8

From pg_restore's documentation you can see: -t table --table=table Restore definition and/or data of named table only. Multiple tables may be specified with multiple -t switches. This can be combined with the -n option to specify a schema. Highlighting "Multiple tables may be specified with multiple -t switches", which means that you can use -t ...


7

It seems that I had to re-run the AWS RDS PostGIS instructions: CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$; SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser') FROM ( SELECT nspname, relname FROM pg_class c JOIN ...


7

I have completed the migration with no problems. Creating the dump is easy: sudo -u postgres pg_dump --verbose --no-tablespaces --format=directory --file=/backup/path old_database_name Restoring on a new instance: first, create a new tablespace, and a target database in that tablespace. Then import your dump like this: sudo -u postgres pg_restore --...


7

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 <file>


7

Fixed this by changing the quotes from "schema_name" to '"schema_name"' See: pg_dump doesn't dump datascheme with uppercase letters in ...where Tom Lane replied: The problem is that there are two levels of quoting needed: one for the shell and one for SQL. As you wrote it, the double quotes are stripped off by the shell and so pg_dump gets Schema, ...


6

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 lines that contain this literal text.


6

Why are the access privileges on the database itself not restored? It's a bug, or a design oversight. Though the responder to that report doesn't think so. pg_dumpall --globals-only doesn't dump rights on the database. Neither does pg_dump as part of the database dump. So grants on databases only get included in a full pg_dumpall. I'll make some noise ...


6

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 confusing: Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects ...


6

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 been the underlying cause. The commands were accepted, but there could have been a corruption issue there.


6

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."Foo"(); CREATE TABLE nd@postgres=# \! pg_dump --schema=foobar -t "Foo" pg_dump: no matching tables were found So you should to fully qualify ...


6

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's impossible to create a database if you're not already connect to another database. This is documented as: When this option is used, the database named ...


6

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> ; TOC Entries: 2615 ; Compression: -1 ; Dump Version: 1.13-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from ...


5

If the data is smallish, I would just do the pg_dump, restore it to a temporary just-for-this-purpose database server, rename the schema within that temporary server (ALTER SCHEMA...RENAME...), and dump it out of that temporary server to do the final restore.


5

The main indicator is the exit status of the pg_dump command. If it's non-zero, then something went wrong otherwise it worked all along. This is the implicit contract between any command and the shell, and breaching it would be a bug. And it's also testable when the sub-command could't be launched at all. Here's a basic skeleton in Unix shell that tests the ...


5

Using PostgreSQL 12.4: $ pg_dump --help ... -O, --no-owner skip restoration of object ownership in plain-text format ... -x, --no-privileges do not dump privileges (grant/revoke) Here's an example: $ pg_dump -O -x mydb_development | gzip > mydb-2020-09-22.bak.gz


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