Hot answers tagged

6

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


3

The question you are referencing is a bit of a mess, I think you would be better off not referring to it at all. It just introduces misconceptions to then partially remove some of them. If you want to backup and restore the whole cluster, you should use "pg_dumpall", not "pg_dump". Using "pg_dumpall" without any of the restrictive options will emit the ...


2

Following worked for me pg_dump "port=<port> host=<host> user=<user dbname=<db> sslcert=<cert> sslkey=<key> sslrootcert=<ca.crt> sslmode=verify-ca" -f <file>


2

Use the -L flag with pg_restore after taking a dump in a custom file format. -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored. ...


2

pg_dump can only dump a single DB. The manual: pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall. pg_dumpall, on the other hand can only dump all DBs in the cluster, even if you provide a specific DB name in the ...


1

I had also a similar case and I solved it via doing theese steps: First of all I made an initial psql_dump with the tables I wanted to dump: pg_dump -t my_table1 -t mytable2 > my_dump.sql Then I listed any sequence existing by filtering them, from the existing dump I made. In case of larger dumps use tools offered in bash or in powershell in order to ...


1

The pg_dump version is pg_dump (PostgreSQL) 9.3.23 Is that the program which was used to generate the "dirfmrt" directory? It doesn't matter what pg_dump you have install locally, if the backup file came from somewhere else. The version of the running postgresql server which was dumped, and the version of pg_dump used to dump it, are included in the dump. ...


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

CREATE TABLE destschema.dest_table AS SELECT fid, wkb_geometry FROM dblink('host=localhost user=postgres... This statement could be used to transfer between databases on the same machine, but not across different machines. When you're logged to the AWS database, localhost designates the AWS host, not your PC. This is why the credentials ...


1

Question: How to prevent this error or work around it? fsync ensures that the data is physically written to disk, so that in case of a crash or loss or power a few seconds after the backup, no data is lost in unflushed memory buffers. Unfortunately it doesn't seem to work in your case. You may add --no-sync to avoid this step, although the errors shown are ...


1

Create a txt file with database names, one per line like: db1 db2 db3 and so on for all db, then from shell: for a in `cat filename.txt`; do pg_dump -d $a > /tmp/${a}_backup.out; done by M. P.S. I'm not able to write correctly (cannot understand why) but the filename.txt MUST be enclosed by "grave accent" (code U+0060)


1

try this C:\Program Files\PostgreSQL\9.6\bin>psql -U postgres dbname < D:\testdump.sql or C:\Program Files\PostgreSQL\9.4\bin>psql -U postgres dbname < D:\testdump.sql


1

just after being a superuser of the shell, try with supplying superuser postgres as: pg_dumpall -f filename.sql -U postgres or pg_dumpall -U postgres > filename.sql you do not need $bash-rc, just be superuser and run the commands, more info is here


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