Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now
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 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 ...


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

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


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

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

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


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

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


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

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

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


1

With the directory format (-F d) of pg_dump, you can parallelize dump and restore, which will help if there are several large tables.


1

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


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


1

I would suggest you try to do something along the line of: SELECT count(*) FROM mytable WHERE my_primary_key < value ; ... and find at which point it fails. [This may be tedious, but you can just cut in half repeatedly, until you find the value that first fails.] You will most probably need to alter the different settings that affect index usage, ...


1

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.


1

What you probably want is to restore all things that are not data. So try -s, from man pg_restore -s --schema-only Restore only the schema (data definitions), not data, to the extent that schema entries are present in the archive. This option is the inverse of --data-only. It is similar to, but for historical reasons not identical to, ...


1

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


1

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


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