Re: 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(dot)klaver(at)aklaver(dot)com