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.


9

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


9

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


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.


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

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

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


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


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

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

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


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

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


3

I'll post an answer because it might be useful to someone else. As the screenshot in the question displays, there is an error in the /home/openproject/.postgresqlrc file on the server on 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, ...


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

You can set connection limit for role: ALTER ROLE x SET CONNECTION LIMIT 0; or for databases: ALTER DATABASE db SET CONNECTION LIMIT 0; After your maintenance chores are done, reset your limit to its previous value such as -1 for unlimited connections.


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

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


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

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

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

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

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


1

Exactly one file format is supported by pg_restore: the custom PostgreSQL dump formats from pg_dump -F c (custom), -F d (directory) and -F t (tar). Notably it cannot restore -F s (SQL) format dumps, which must be passed to psql -f instead. If you want to load data, you probably want the \copy command from psql or the server-backend COPY command. It can load ...


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