CODEWITHSUNDEEP
sqlpostgresqlpsqlrestorezcat
Michu93
Michu93

Reputation: 5725

Run restore on working database. What happens?

What happens when I runned:

zcat /mnt/Postgres/restoreFile.gz | psql my_db

on the working database and after doing ALTER TABLE and other standard things there were problems with duplicated keys. When I stopped it and tried to insert into database then I got duplicates key error because of sequences and constraints. Seems like all data is in but what about the sequences. What really happend with that database?

Upvotes: 0

Views: 89

Answers (1)

Andomar
Andomar

Reputation: 238246

A normal Postgres backup consists of table design (like create table) and data (like insert) statements. If you run it twice, most design statements will fail. The insert statements would succeed in so far as the data definition allows for duplicate rows.

So restoring a database to a production server would typically result in a lot of duplicate rows in tables without a primary key. Some design changes made after the backup (like changing the owner of a table) may be undone.

Upvotes: 1

Related Questions