Table update: restore or replace?

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Table update: restore or replace?

Rich Shepard
I just updated a column in a table (postgresql-10.5) and accidently applied
it to all 185 rows rather than just one. There is no active transaction to
roll back.

Each night I do a backup and just started working on the database a few
minutes ago. If I restore all contents of
/var/lib/pgsql/10/data/ will this restore the database's state before the
botched update and two previous updates today?

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Adrian Klaver-4
On 5/14/19 1:46 PM, Rich Shepard wrote:
> I just updated a column in a table (postgresql-10.5) and accidently applied
> it to all 185 rows rather than just one. There is no active transaction to
> roll back.
>
> Each night I do a backup and just started working on the database a few

A file level backup or database dump?

> minutes ago. If I restore all contents of
> /var/lib/pgsql/10/data/ will this restore the database's state before the
> botched update and two previous updates today?
>
> Rich
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Rich Shepard
On Tue, 14 May 2019, Adrian Klaver wrote:

> A file level backup or database dump?

Adrian,

File level. April 20th is my most recent database dump because I forgot to
run it last Friday afternoon.

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Adrian Klaver-4
On 5/14/19 1:59 PM, Rich Shepard wrote:
> On Tue, 14 May 2019, Adrian Klaver wrote:
>
>> A file level backup or database dump?
>
> Adrian,
>
> File level. April 20th is my most recent database dump because I forgot to
> run it last Friday afternoon.

Does the table you overwrote the data change much?

If not it might be safer to just fetch it from the April 20th dump and
then apply the changes since then.


If you where to restore at the file level I would:

1) Stop the server.

2) Move the existing content of /var/lib/pgsql/10/data/ somewhere else
just to be safe.

3) Copy in the backup.



>
> Rich
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Tom Lane-2
Adrian Klaver <[hidden email]> writes:
> If you where to restore at the file level I would:
> 1) Stop the server.
> 2) Move the existing content of /var/lib/pgsql/10/data/ somewhere else
> just to be safe.
> 3) Copy in the backup.

Yeah.  You can't just selectively copy files, because the data files
are dependent on the contents of the pg_xact transaction log; it's
all or nothing.

Also, I don't know what method you've been using to make file-level
backups, but they're really pretty worthless unless you (a) stop
the server or (b) use a filesystem snapshot.  Otherwise you're very
likely to have inconsistent data.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Rich Shepard
In reply to this post by Adrian Klaver-4
On Tue, 14 May 2019, Adrian Klaver wrote:

> Does the table you overwrote the data change much?

Adrian,

Yes. It's in my business tracking database so it's updated almost every day.

> If not it might be safer to just fetch it from the April 20th dump and
> then apply the changes since then.

The column I FUBAR'd holds e-mail addresses. What I'll do is set all to NULL
and find the e-mail addresses I have external to the database and enter them
in a large UPDATE TABLE.

And I think I'll set up a cron job to do a database dump each day with the
date appended to the file name in the bash shell script.

Thanks,

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Rich Shepard
In reply to this post by Tom Lane-2
On Tue, 14 May 2019, Tom Lane wrote:

> Yeah. You can't just selectively copy files, because the data files are
> dependent on the contents of the pg_xact transaction log; it's all or
> nothing.

Tom,

That's why I thought of copying the entire data/ directory.

> Also, I don't know what method you've been using to make file-level
> backups, but they're really pretty worthless unless you (a) stop the
> server or (b) use a filesystem snapshot. Otherwise you're very likely to
> have inconsistent data.

I run dirvish <http://www.dirvish.org/> which runs each night starting at
00:30 am when there's no activity (by me, at least) on the database.

Regards,

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Re: Table update: restore or replace?

Brad Nicholson-2
In reply to this post by Rich Shepard

> From: Rich Shepard <[hidden email]>
> To: [hidden email]
> Date: 05/14/2019 05:19 PM
> Subject: [EXTERNAL] Re: Table update: restore or replace?

> And I think I'll set up a cron job to do a database dump each day with the
> date appended to the file name in the bash shell script.

Might I suggest you setup proper backups with continuous archiving instead?  If you had those, you would be able to restore this database back to the point right before you issued the bad update statement.

I'd highly recommend pgBackRest for the task.

Brad.

Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Adrian Klaver-4
In reply to this post by Rich Shepard
On 5/14/19 2:19 PM, Rich Shepard wrote:

> On Tue, 14 May 2019, Adrian Klaver wrote:
>
>> Does the table you overwrote the data change much?
>
> Adrian,
>
> Yes. It's in my business tracking database so it's updated almost every
> day.
>
>> If not it might be safer to just fetch it from the April 20th dump and
>> then apply the changes since then.
>
> The column I FUBAR'd holds e-mail addresses. What I'll do is set all to
> NULL
> and find the e-mail addresses I have external to the database and enter
> them
> in a large UPDATE TABLE.

Or just fetch them from the table data you have saved in the dump file.

>
> And I think I'll set up a cron job to do a database dump each day with the
> date appended to the file name in the bash shell script.

Yeah, that will save a lot of heartburn:)

>
> Thanks,
>
> Rich
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Ron-2
In reply to this post by Rich Shepard
On 5/14/19 3:59 PM, Rich Shepard wrote:
On Tue, 14 May 2019, Adrian Klaver wrote:

A file level backup or database dump?

Adrian,

File level. April 20th is my most recent database dump because I forgot to
run it last Friday afternoon.

Note that referring to file level copies as dumps can be very confusing in the Postgres world because the pg_dump command does logical backups, and they're called dump files.

--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Re: Table update: restore or replace?

Rich Shepard
In reply to this post by Brad Nicholson-2
On Tue, 14 May 2019, Brad Nicholson wrote:

> Might I suggest you setup proper backups with continuous archiving
> instead? If you had those, you would be able to restore this database back
> to the point right before you issued the bad update statement.
>
> I'd highly recommend pgBackRest for the task.

Brad,

I'll certainly take a look at pgBackRest.

Thanks,

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Rich Shepard
In reply to this post by Adrian Klaver-4
On Tue, 14 May 2019, Adrian Klaver wrote:

> Or just fetch them from the table data you have saved in the dump file.

Adrian,

Sigh. I should have thought of that. A great time saver.

Thanks,

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace? [RESOLVED]

Rich Shepard
In reply to this post by Adrian Klaver-4
On Tue, 14 May 2019, Adrian Klaver wrote:

> Or just fetch them from the table data you have saved in the dump file.

Adrian,

This did save a lot of time! Thanks for the pointer.

>> And I think I'll set up a cron job to do a database dump each day with the
>> date appended to the file name in the bash shell script.

> Yeah, that will save a lot of heartburn:)

Done. Cron will run the shell script at 02:15 each day. This will do until I
build, install, and configure pgBackRest (which consumes less disk space
than do the daily pg_dumpall files.

Best regards,

Rich


Reply | Threaded
Open this post in threaded view
|

Re: Table update: restore or replace?

Stephen Frost
In reply to this post by Rich Shepard
Greetings,

* Rich Shepard ([hidden email]) wrote:
> That's why I thought of copying the entire data/ directory.

That isn't going to work because things change in the data directory...

> >Also, I don't know what method you've been using to make file-level
> >backups, but they're really pretty worthless unless you (a) stop the
> >server or (b) use a filesystem snapshot. Otherwise you're very likely to
> >have inconsistent data.
>
> I run dirvish <http://www.dirvish.org/> which runs each night starting at
> 00:30 am when there's no activity (by me, at least) on the database.

The database system is potentially doing things though, so this isn't a
backup solution that is reliable.  You really should be using a backup
solution that's been specifically written to work with PostgreSQL.

I wouldn't trust performing a restore from a backup taken like this.
I'd suggest you restore to a new server (or another directory, at
least...) and try starting up PG and then dump out the table and then
check that it's valid.

And then switch to a backup system that actually works with PG.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment