Dump postgres database to file

This will produce dump file sizes similar to using gzip , but it has the added advantage that tables can be restored selectively. The following command dumps a database using the custom dump format:. For very large databases, you might need to combine split with one of the other two approaches. Development Versions: devel. Unsupported versions: 9. This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the other supported versions listed above instead. PostgreSQL 9. Backup and Restore Next.

SQL Dump The idea behind this dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How to dump a postgres DB into a. Asked 5 years, 7 months ago. Active 4 years, 7 months ago. Viewed 5k times. Improve this question.

NoobNe0 NoobNe0 1 1 gold badge 6 6 silver badges 20 20 bronze badges. A better option in many situations can be the "-1" the number one or "--single-transaction" option:. This can be a costly trade-off for larger restorations, but in many cases, the benefit of not leaving you with a partially restored database heavily outweighs that cost.

Instead, the command backs up every available database:. Backups are an essential component in any kind of data storage plan. Fortunately, PostgreSQL gives you the utilities necessary to effectively backup your important information.

As with any kind of backup, it is important to test your backups regularly to ensure the copies that are created can be restored correctly. The backups you create are only useful if they can actually be used to recover your system. Where would you like to share this to? Twitter Reddit Hacker News Facebook. Share link Tutorial share link.

Sign Up. Do not output commands to set ownership of objects to match the original database. These statements will fail when the script is run unless it is started by a superuser or the same user that owns all of the objects in the script. To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O. Specify the superuser user name to use when disabling triggers. This is only relevant if --disable-triggers is used.

Usually, it's better to leave this out, and instead start the resulting script as superuser. Dump only tables or views or sequences or foreign tables matching table. Multiple tables can be selected by writing multiple -t switches. The -n and -N switches have no effect when -t is used, because tables selected by -t will be dumped regardless of those switches, and non-table objects will not be dumped.

Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database. Note: The behavior of the -t switch is not entirely upward compatible with pre Formerly, writing -t tab would dump all tables named tab , but now it just dumps whichever one is visible in your default search path.

Also, you must write something like -t sch. Do not dump any tables matching the table pattern. The pattern is interpreted according to the same rules as for -t. When both -t and -T are given, the behavior is to dump just the tables that match at least one -t switch but no -T switches. If -T appears without -t , then tables matching -T are excluded from what is otherwise a normal dump. Specifies verbose mode. Specify the compression level to use.

Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip ; but the default is not to compress.

The tar archive format currently does not support compression at all. This option is for use by in-place upgrade utilities. Its use for other purposes is not recommended or supported. The behavior of the option may change in future releases without notice. This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non- PostgreSQL databases.

However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents.

This option disables the use of dollar quoting for function bodies, and forces them to be quoted using SQL standard string syntax. This option is only relevant when creating a data-only dump. Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

Presently, the commands emitted for --disable-triggers must be done as superuser.