Table level restore in postgres

On 3/28/24 11:27, arun chirappurath wrote:

Dear all,

I am a new bie in postgres world....

Suppose I have accidently deleted a table or deleted few rows ,is it
safe to drop this table and restore just this table from custom backup
to same database?

1) You can though depending on when you took the backup it might not be
up to date.

2) Do you have replication(logical or binary) set up?

3) Do you know what was deleted?

Or should I create a new database and restore it there and then migrate
the data?

That is overkill for a single table.

What is the general methodology used?

One way, create a new table that has the same structure as the one you
want to restore, do a data only dump from the backup, rename the table
name in the dump output to the new table name and restore the data to
the new table, verify the data and then transfer all or part of the to
existing table.

I tried it in a smaller database and it worked in same database..however
dbeaver was throwing a warning saying database may get corrupted?

1) DBeaver thinks a lot of things are wrong that are not, I would use
the tools that ship with Postgres; psql, pg_dump, pg_restore, etc.

2) If you want to stay with DBeaver post the actual complete error
message here.

Thanks,
Arun

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Mar 28, 2024 at 2:27 PM arun chirappurath <arunsnmimt@gmail.com>
wrote:

Dear all,

I am a new bie in postgres world....

Suppose I have accidently deleted a table or deleted few rows ,is it safe
to drop this table and restore just this table from custom backup to same
database?

By "custom backup", do you mean a dump file created by "pg_dump
--format=custom"?

Or should I create a new database and restore it there and then migrate
the data?

What is the general methodology used?

If you backup the database using "pg_dump --format=custom", then yes, you
can use
pg_restore --format=custom --table=${sometable} --database=$yourdatabase

I'd test it first, though, in a scratch database (with scratch tables and
scratch data), just to ensure that the command doesn't first drop the
database.