CODEWITHSUNDEEP
postgresqlpg-dumppg-restorepg-upgradepg-dumpall
manasa
manasa

Reputation: 21

Need some clarification on dropping database during pg_restore

When using pg_dump with the clean option, it generates SQL statements tailored to drop objects when restoring with pg_restore (pg_dump custom format file contains 'DROP DATABASE'). If pg_dump doesn't include clean, but pg_restore does, what are the implications? Pg_upgrade uses both pg_dump and pg_restore. I encountered a SQL command in the dump file to drop the postgres database.

These are the comments from the source file of pg_dump;

We assume that "template1" and "postgres" already exist in the target installation. dropDBs() won't have removed them, for fear of removing the DB the restore script is initially connected to. If --clean was specified, tell pg_dump to drop and recreate them; otherwise we'll merely restore their contents. Other databases should simply be created.

I'm uncertain about the handling of template1 during such operations. Since template0 isn't meant for data connection and is not dropped, how does the process deal with template1, especially considering that pg_restore typically connects using template1. there can be issues if pg_restore attempts to drop template1 while connected to it.

Upvotes: 0

Views: 381

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247535

The --create and --clean options of pg_dump are only effective in the plain format. For custom format dumps, they have no effect. It is the options that you use with pg_restore that influence what happens.

Upvotes: 2

Related Questions