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


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

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

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


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


4

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


4

If pg_dump can't seek in the output file (typically meaning the output was piped somewhere) it can't update the output file with certain metadata and silently leaves it out of the dump. Changes to the pg_restore -j logic in PostgreSQL 12 made it depend on that data and when it's not available you get the possibly due to out-of-order restore request error. ...


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


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

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.


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

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

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

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

When I tried to install via Azure Data Studio I got an error: Restore: pg_restore: [archiver] unsupported version (1.13) in file header Version 1.13 of the pg_dump archive format was introduced in February 2018 as part of the changes for CVE-2018-1058. The version bump was mentioned in the commit message with a specific warning about the problem: Since ...


1

The reason is that type casts do not live in a schema. If you specify -n public, you will only get objects from that schema, and that does not include the type cast.


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

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


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


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


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