Restoring Catmaid data

[John Roche]

Hi,

I managed to get Catmid up and running and the users are now tracing data etc..

one of the concerns is restoring data in the event of server failure etc..

from the documentation we backed up the data and then did some tracing 

to see if when restoring the sql data would revert the old changes but

when we did nothing happened 

The commands I used are 

pg_dump -Fc --clean -U catmaid -f catmaid_dump.sql

psql -U -d catmaid -f catmaid_dump.sql 

my question is 

is there a way to restore everything to the last changes if we need to?

John

[Mark Longair]

Hi John,

One problem with the restore command you've quoted is that the
database username is missing after -U. However, even with that,
I think this would have failed because with the -Fc option
pg_dump creates a binary archive, and they have to be restored
with pg_restore rather than psql. (psql can only be used to
restore a plain text SQL database dump.) This looks like a
mistake in the documentation to me:

https://catmaid.readthedocs.io/en/stable/administration.html#backup-and-restore-the-database

In the git history, it seems that e19b0c38742c90832 added -Fc to
the pg_dump invocation (a good idea) but didn't update the
suggested restore command at the same time.

Best regards,
Mark

P.S. Personally, I'd try doing the test restore into a new
database first so you don't wipe out any tracing that users have
done since the database dump and because it'll be safer if
anything goes wrong.

[John Roche]

Hi Mark

sorry my mistake with the commands were 

  psql -U catmaid_user -d catmaid -f catmaid_dump.sql

  pg_restore catmaid_dump.sql

we are still in proof of concept mode so we are just using one database for now

but even when we restore catmaid_dump.sql the tracking data that we thought would be back 

didn't come back.

John

[Tom Kazimiers]

Hi Mark and John,

On Mon, Oct 21, 2019 at 02:08:38PM +0100, Mark Longair wrote:
>John Roche <john.ro...@gmail.com> wrote:
>> one of the concerns is restoring data in the event of server failure
>> etc..

Definitely a very good idea! It's important to actually test if backups
work.

>> from the documentation we backed up the data and then did some
>> tracing to see if when restoring the sql data would revert the old
>> changes but when we did nothing happened

Was there an error shown by psql? Mark is completely right though, the
documentation shows the wrong command and it should be something like:

pg_restore -U <CATMAID-USER> -d catmaid catmaid_dump.sql

>the database username is missing after -U

Good catch, this is already fixed in the current dev branch, where
<CATMAID-USER> is used as a placeholder in both commands (like above):

https://catmaid.readthedocs.io/en/latest/administration.html

>With the […] -Fc option pg_dump creates a binary archive, and they have
>to be restored with pg_restore rather than psql. […] e19b0c38742c90832

>added -Fc to the pg_dump invocation (a good idea) but didn't update the
>suggested restore command at the same time.

Ah indeed, thanks, I missed that command a few months back. This is
fixed now in dev.

>P.S. Personally, I'd try doing the test restore into a new
>database first so you don't wipe out any tracing that users have
>done since the database dump and because it'll be safer if
>anything goes wrong.

I agree! Makes subsequent tests also easier.

Cheers,
Tom