2

I did a backup with pg_dump of a database called database_a (whose PostgreSQL owner was ubuntu) in the server A. I called database_a_bkp.dump to the resultant file.

Afterwards, I went to other server B. In this server B, there was another different database also called database_a, whose PostgreSQL owner was odoo. But I created a new one named database_b, whose owner was ubuntu, to do the restoration there.

I executed the following command:

pg_restore --create --clean -U ubuntu -d database_b database_a_bkp.dump

However, unfortunately, this command altered the database_a, instead of restoring data in database_b. The database_a owner changed to ubuntu. I needed to move the owner to odoo again and do a REASSIGN OWNED query.

But there were more changes. For example, so far I realized that the nextval of the ID pg_sequences has changed, resulting in a lot of errors in database_a.

How is this possible? What was the mistake? I can fix those nextval, but I'm afraid of those are not the only things that the pg_restore has changed. Does anyone know the reason and how many things can have been altered in database_a?

Improve this question

1 Answer 1

Reset to default
1

The problem is the --create. pg_restore connects to database_b, tries to drop database_a (because of --clean), creates it again, connects to it and restores the data.

Omit --create and --clean, and it will do what you want.

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

4 Comments

Do you mean that the database_a of the server B has been destroyed??? I will kill myself if so. But now, in database_a of server B, I can still see records which do not exist in the database_a of the server A. Like if there were a lot of things which pg_restore was not able to drop. Does this mean that it was not dropped?
I mean, why pg_restore with --create would connect to database_b and drop database_a (a database whose owner is other PostgreSQL user). Is because the content inside the database_a_bkp.dump file?
Why it would do that is because of --clean used with --create. Dropping things is what --clean is for. Whether it actually destroyed the database depends on whether 'ubuntu' had permissions to do that, it might have failed. Yes, the name of the database to operate on is stored in the -Fc dump file (but that name is only used with --create, otherwise it is present but unused.)
I have clarified my answer. I can only recommend to read the documentation before you run a command on a database with important data in it.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.