tar SQL dump

[claudiubelu]

Description

In the dump and restore guide [1], it is suggesting to create plain dump.sql dumps, and even the sample SQL dump provided is a plain dump.sql dump.

There are a couple of issues that can arise when trying to restore such a database. These include:

invalid command \N

Or:

ERROR:  syntax error at or near "1"
LINE 1: 1 1 51
        ^
ERROR:  syntax error at or near "1"
LINE 1: 1 1 App Survey Questions about your application APPLICATION ...
        ^

(Note: \N is supposed to be a NULL value).

These problems could be mitigated by having a tar SQL dump instead. Plus, it should be a smaller file size as well.

When doing pg_dump, you can pass in a few extra arguments, including the output format [3] (plain by default). So, creating a tar dump can be done by running:

pg_dump -d {database} -U {user}  --format=tar > dump.sql

Having this tar dump publicly available can then be used by the Juju deployment of the Waltz public instance, in which it will be used to reset the database periodically.

[1] https://github.com/finos/waltz/blob/master/docs/development/database/dump_and_restore.md [2] https://github.com/finos/waltz/releases/download/1.23.1/dump_pg_1.23.zip [3] https://www.postgresql.org/docs/12/app-pgdump.html

Resourcing

We would like to add this request to the Waltz team's feature backlog

[davidwatkins73]

Hi @claudiubelu , Thanks for the detailed notes.

I've attached a dump to the latest (alpha0 build). Let me know if that fixes the issue and I'll update our docs to ensure we run this in the future.

[claudiubelu]

Hi @claudiubelu , Thanks for the detailed notes.

I've attached a dump to the latest (alpha0 build). Let me know if that fixes the issue and I'll update our docs to ensure we run this in the future.

Hello, it seems that the file is a .tar.gz file, and not a .tar. AFAIK, pg_restore requires it to be a .tar file.

Additionally, it seems that something is wrong with the archive:

tar -xzvf postgres-dump.tar
tar: This does not look like a tar archive
tar: Skipping to next header
tar: A lone zero block at 9938
tar: Exiting with failure status due to previous errors

gunzip -d postgres-dump.tar.gz
tar -xvf postgres-dump.tar
tar: This does not look like a tar archive
tar: Skipping to next header
tar: A lone zero block at 9938
tar: Exiting with failure status due to previous errors

I tried 7zip as well, got the same issue.

[davidwatkins73]

Hi @claudiubelu

I've re-done the extract and simply gzipped it this time.
Let me know if this works for you.
I'm going to update our simple docker image on my digital ocean site later. So will be able to double check there as well.

postgres-dump-1.40.sql.gz

[claudiubelu]

Hi @claudiubelu

I've re-done the extract and simply gzipped it this time. Let me know if this works for you. I'm going to update our simple docker image on my digital ocean site later. So will be able to double check there as well.

postgres-dump-1.40.sql.gz

Thanks, it looks to be working. I've sent an update to the finos-waltz-bundle public bundle to include this dump: https://github.com/pedroleaoc/waltz-juju-bundle/pull/2

[davidwatkins73]

Hi, here's another extract with a few modifications (updated intro screen, added a test/password user, changed the admin user default password) etc.

postgres-dump-1.40-1.sql.gz