Table update: restore or replace?

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
adrian.klaver@aklaver.com

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

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
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> 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

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

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/&gt; which runs each night starting at
00:30 am when there's no activity (by me, at least) on the database.

Regards,

Rich

From: Rich Shepard <rshepard@appl-ecosys.com>
To: pgsql-general@lists.postgresql.org
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.

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
adrian.klaver@aklaver.com

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.

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

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

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

Greetings,

* Rich Shepard (rshepard@appl-ecosys.com) 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/&gt; 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