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=...
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 ...
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
Output a plain-text SQL script file (the default).
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).
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 ...
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.
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 (...
From pg_restore's documentation you can see:
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 ...
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 ...
I think you need to use --exclude-table-data=table option. From the docs:
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 ...
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.
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 ...
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 --...
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 ...
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 ...
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')
SELECT nspname, relname
FROM pg_class c JOIN ...
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 ...
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.
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, ...
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;
nd@postgres=# create table foobar."Foo"();
nd@postgres=# \! pg_dump --schema=foobar -t "Foo"
pg_dump: no matching tables were found
So you should to fully qualify the table name:
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
In turn, the state of the database that you see on the slave is also consistent. If there ...
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 ...
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(
"-h" , "localhost",
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(...) ...
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.
At the row-level storage, large values are stored as pointers to tables in the pg_toast schema containing ...
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 ...
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 ...