Archive for the ‘postgresql’ Category

parallel pg_dump pg_restore JOBS

26 septiembre, 2017 Deja un comentario



Easy way to view postgresql dump files

If the dump is not plain text – try using pg_restore -l your_db_dump.file command. It will list all objects in the database dump (like tables, indexes …).

Another possible way (may not work, haven’t tried it) is to grep through the output of pg_restore your_db_dump.file command. If I understood correctly the manual – the output of pg_restore is just a sequence of SQL queries, that will rebuild the db.

Categorías:BBDD, postgresql, Uncategorized

LOG pg_dump

pg_dump.exe -h my_host -p my_port -U my_user -w -Fc -b -v -f  C:\testbackup
postgres >>C:\testlog.txt 2>>C:\testlog.txt

PostgreSQL Examples to Install, Create DB & Table, Insert & Select Records

Mini Hack: Parallel Vacuuming in PostgreSQL

Categorías:BBDD, postgresql Etiquetas: ,

How to get a working and complete PostgreSQL DB backup and test

You can dump the whole PostgreSQL cluster with pg_dumpall. That’s all the databases and all the globals for a single cluster. From the command line on the server, I’d do something like this. (Mine’s listening on port 5433, not on the default port.) You may or may not need the –clean option.

$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql

This includes the globals–information about users and groups, tablespaces, and so on.

If I were going to backup a single database and move it to a scratch server, I’d dump the database with pg_dump, and dump the globals with either

  • pg_dumpall –globals-only, or
  • pg_dumpall –roles-only (if you only need roles)

like this.

$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql

Outputs are just text files.

After you move these files to a different server, load the globals first, then the database dump.

$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql

I thought pg_dumpall would at least backup foreign keys, but even that seems to be an ‘option’. According to: even with pg_dumpall I need to use a -o option to backup foreign keys

No, that reference says “Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used.” (Emphasis added.) I think it’s unlikely that your application references the OID columns. You don’t need to use this option to “backup foreign keys”. (Read the dump file in your editor or file viewer.)