15

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.


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

The manual about the money type has advice for your case exactly: Since the output of this data type is locale-sensitive, it might not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump into a new database make sure lc_monetary has the same or equivalent value as in the ...


7

Let's define a couple of variables to make the rest easier to copy/paste (using names from the original question) old_db=myapp new_db=myapp3 db_dump_file="backups/myapp_2018-05-27.pg.dump" The following assumes that your backup was created with the "custom" format like this: pg_dump -U postgres -F custom "$old_db" > "$db_dump_file" To restore $...


7

The database the dump file was taken from has a certain foreign key constraint defined. But your target database does not. Probably because someone went and added it to the dumped database since the last dump. Since you specified --clean, it is trying to drop all the objects that exist in the dump file from your target database before recreating them. It ...


6

I found the line in the toc.dat file that made the call and unpacked the .dat.gz file to find it only contains "\." (sans the quotes) in it. \. is not a command, it marks the end of data in a COPY stream. If the corresponding data file inside the dump contains only that, then the source table was empty. Also note that you can pg_restore --list to list ...


5

When you restore the data, use pg_restore --section=pre-data --section=data. This will leave off the indexes. Then run the UPDATEs you want to, then run pg_restore --section=post-data


5

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.


5

UPDATE: the "a worker process died unexpectedly" issue was fixed upstream in PostgreSQL 12.4! Upgrade to get the fix. For the best compatibility and performance with pg_restore have your pg_dump write the dump file to a local file on disk instead of an unseekable file descriptor.


4

These are the exact steps I take to import a copy of a PostgreSQL 9.5 database (exported on Linux using en_US.UTF-8 encoding) into PostgreSQL 9.3 on Windows 7 or PostgreSQL 9.5 on Windows 8.1. You need to create the database with the appropriate encoding prior to loading the SQL file, otherwise the encoding from Linux (en_US.UTF-8) will prompt Windows to use ...


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

It's probably just database contention. Doing a parallel dump, you postgres can write to many different files at once, so the load can be spread around. Doing a parallel restore, postgres has to manage all that [heavy-weight] traffic coming in from jobs+1 connections, all filling up data pages in the buffer cache and writing them to disk, all at once. ...


3

If you are using Windows and are using RDP to connect to the server you can just select the dump file, press CTRL-C, switch to the server connection and browse to a folder where you would like to copy the file to and press CTRL-V. If your laptop and the server are on the same network, you can create a file share on either your laptop or on the server (or ...


3

Bit of a FAQ. pg_upgrade with --link (but understand that you can't easily go back); Logical streaming replication with pglogical Trigger-based logical replication with Londiste etc Search for zero or low downtime PostgreSQL major version upgrade.


3

I'll post an answer because it might be useful to someone else. As the screenshot displays, there is an error in the /home/openproject/.postgresqlrc file in the server in which I've run the backup. The format of that file was not correct: I've fixed it by following the format described here and when I had run the next backup I get no errors, and I was also ...


2

restore.sql is a plain SQL statement file in your "dvdrental.tar" file. First create your db with the statement below; CREATE DATABASE db_name ENCODING = 'UTF8'; After that, instead of using pg_restore you can use psql. psql --echo-all --file=restore.sql db_name


2

Try specifying the name of the DB to start in, using the -d or --dbname argument. This worked for me when I had the same problem. Note that it doesn't have to be the target DB: pg_restore -h localhost -p 5434 -U postgres -d postgres -W -C -v z:\pg_dump\96_junk.dump


2

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


2

I managed to resolve these errors by doing 3 things: 1. Removing all definitions that are already defined in the PostGIS extension from the database dump All of the function, operator family, and operator class definitions are already included in the new database because I installed PostGIS on the new database. This is also true of the spatial_ref_sys ...


2

In order to create a database, it needs to be able to connect to your database server in order to issue the "CREATE DATABASE" command. Connecting to the server requires you to specify an existing database to connect under, and obviously that cannot be the database which fails to exist and which is to be created. There are special databases usually reserved ...


2

The parenthesis is special to the shell. You don't have to remove it, you have to quote/escape it to get it to pass through the shell. pg_restore -d postgres --function='addconfiguration2(text)' -v /tmp/dump


2

If you run pg_dump with the --clean option then the generated SQL dump would contain drop statements for all dumped objects (note that when using the custom format and pg_restore you don't need to decide on this when dumping, but when restoring). However if the target database contains tables (or other objects) that were not contained in the source, those ...


2

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 create statements to insert your data and the dead rows would anyways be invisible to it. If you have lots of updates and deletes happening or in other words your db ...


2

Yes, dumping and restoring the database will have the same effect as VACUUM (FULL). But having to do either of these every day is painful. You have too little storage for your database. Artificially keeping it compact conserves space and speeds up queries, but it may slow down data modifications. The proper solution is to increase your storage and give your ...


1

Use pg_dump --schema option for this. With: $ psql psql (9.4.19) Type "help" for help. mevzek=# CREATE SCHEMA test1; CREATE SCHEMA mevzek=# CREATE TABLE test1.atable (id SERIAL); CREATE TABLE mevzek=# \q You can compare the results of the two commands: $ pg_dump --table test1.atable > dump1.sql $ pg_dump --schema test1 > dump2.sql $ diff -u dump1....


1

Evidently, "root" is not a superuser (which is a bit odd, given the normal implications of the word "root"). The pg_restore command you wish to run must be run as a superuser. Only a superuser can drop someone else's database, and then create a new database owned by someone else. Although it looks like the restored database is going to be owned by "root", ...


1

1) First switch to superuser su - postgres 2) then created a new database createdb myapp3 --DROP is already created 3) now change the director to backup file path i.e cd db/backups/ 4) Now just run this to restore ur dump custom file pg_restore -j 8 -U postgres -d myapp3 myapp_2018-05-27.pg.dump 5) If still unable to restore the database kindly take a ...


1

Figured out the problem. I was trying to import a DB dump from Postgres 9.6 to Postgres 10. Downgraded to Postgres 9.6 and it imported successfully.


1

Have you tried parallel pg_restore, i.e. the -j flag? fsync=off can be better than just synchronous_commit=off. In fact, the latter might not do much at all because most of the loading should be done in large COPY commands which wouldn't be committing until the end anyway. You can also try increasing max_wal_size. Finally, use tools like top, vmstat, sar,...


1

What I would do is, Start a transaction. Update all constraints on the database you're merging in to ON UPDATE CASCADE Set all of the ids to be offset by the max(id) of the table you're merging into, with a comfortable margin (so you know they won't overlap at the time of merger) Merge them in. Restart the sequence at a number equal to or greater than the ...


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