Hot answers tagged

33

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


27

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


24

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


23

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


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.


15

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, '...


13

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 (...


13

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


12

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


10

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


10

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.


10

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


9

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


8

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


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, ...


7

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 the example of dumping using the pv and split utilities to keep the big dump files in small chunks. It might be handy to transfer it later to another location. ...


7

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

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

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

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

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

According to a grep in the sources, this error ERROR: compressed data is corrupt happens in case of a decompression failure of a LZ-compressed TOAST'ed value. See http://doxygen.postgresql.org/tuptoaster_8c.html#abcb4cc32d19cd5f89e27aeb7e7369fa8 At the row-level storage, large values are stored as pointers to tables in the pg_toast schema containing ...


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