Need some clarification on dropping database during pg_restore

Question

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.

Laurenz Albe · Accepted Answer · 2024-02-23 07:20:37Z

2

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.

answered Feb 23, 2024 at 7:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

manasa Over a year ago

Ok. Regarding pg_restore and its handling of template1: does it perform a drop operation on the template1 database? If it does, what's the mechanism behind this action? I observed pg_restore connects to template1 using logs.

Laurenz Albe Over a year ago

pg_dump only dumps a single database. If you dump template1 and use --clean and --create, I guess it will drop and re-create the database upon restore. I have no idea why you would want to do that, though.

Collectives™ on Stack Overflow

Need some clarification on dropping database during pg_restore

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related