Hot answers tagged

55

You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster. From the command line on the server, I'd do something like this. (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option. $ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=...


18

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


17

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


16

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


16

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 (per documentation): Currently, CHECK expressions cannot contain subqueries ...


11

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


10

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


10

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.


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

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


8

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


8

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.


7

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


7

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


7

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


6

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


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

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


5

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


5

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


5

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 the table name: nd@...


4

Just run pg_dump. The documentation says: pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). In turn, the state of the database that you see on the slave is also consistent. If there ...


4

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.


4

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


4

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment: ProcessBuilder builder = new ProcessBuilder( "C:/postgres9/bin/pg_dump.exe", "-U", "myuser", "-h" , "localhost", "mydb"); ...


4

When dumping a schema with the mentioned command, as postgres user: pg_dump -f file.sql -F plain --inserts -n schemaname database it produces a file whose basic structure looks like: -- various SET commands CREATE SCHEMA schemaname; ALTER SCHEMA schemaname OWNER TO ownername; SET search_path = schemaname, pg_catalog; CREATE TABLE tablename(...) ... ...


4

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


4

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 error!


4

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


3

Adam, First that is old document -- new one is here: http://postgis.net/docs/AddGeometryColumn.html Though what they say in both is the same. The using that function is only need for 1.5 and below. 2.0 we encourage you to just create the table normally. something like CREATE TABLE test(gid serial primary key, geom geometry(POLYGON, 4326) ); pg_dump ...


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