Restoring backup fails due to Postgres version mismatch

Installation
1

I have created backup of Discourse using web UI on Ubuntu with docker-installed Discourse. Now I want it to restore on my development machine Ubuntu (finally - no docker, no vagrant :slight_smile: ) and I’m getting errors when importing database:

[2016-07-20 10:48:53] 'overgrow' has started the restore!
[2016-07-20 10:48:53] Marking restore as running...
[2016-07-20 10:48:53] Making sure /home/overgrow/discourse/tmp/restores/default/2016-07-20-104852 exists...
[2016-07-20 10:48:53] Copying archive to tmp directory...
[2016-07-20 10:48:53] Unzipping archive, this may take a while...
[2016-07-20 10:48:53] Extracting metadata file...
[2016-07-20 10:48:53] Validating metadata...
[2016-07-20 10:48:53]   Current version: 20160719002225
[2016-07-20 10:48:53]   Restored version: 20160719002225
[2016-07-20 10:48:53] Extracting dump file...
[2016-07-20 10:48:53] Restoring dump file... (can be quite long)
[2016-07-20 10:48:53] SET
[2016-07-20 10:48:53] SET
[2016-07-20 10:48:53] SET
[2016-07-20 10:48:53] SET
[2016-07-20 10:48:53] SET
[2016-07-20 10:48:53] SET
[2016-07-20 10:48:53] psql:/home/overgrow/discourse/tmp/restores/default/2016-07-20-104852/dump.sql:16: ERROR:  unrecognized configuration parameter "row_security"
[2016-07-20 10:48:53] psql:/home/overgrow/discourse/tmp/restores/default/2016-07-20-104852/dump.sql:18: ERROR:  current transaction is aborted, commands ignored until end of transaction block
... (lots of lines like this)

[2016-07-20 10:48:54] psql:/home/overgrow/discourse/tmp/restores/default/2016-07-20-104852/dump.sql:5230: invalid command \N
... (lots of lines like this)

[2016-07-20 10:48:57] psql:/home/overgrow/discourse/tmp/restores/default/2016-07-20-104852/dump.sql:7456: invalid command \n

Maybe previous dump of database is in another format? Different psql versions? Sorry I’m new to psql and rails.
“invalid command \n” maybe string escaping problem? or different EOL?
Any help is really appreciated!

2

I’ve created new backup on development machine to compare dump.sql and looks like database version is different…

-- Dumped from database version 9.5.3
-- Dumped by pg_dump version 9.5.3
-- Started on 2016-07-19 12:42:45 UTC

vs.

-- Dumped from database version 9.3.13
-- Dumped by pg_dump version 9.3.13
-- Started on 2016-07-20 11:16:02 CEST

So backup made on newer pg can’t be imported… Any chance to make SQL dumps from different versions compatible? MySQL has its compatibility mode - is there equivalent in Postgres?

3

You could try removing the lines that PostgreSQL 9.3 is complaining about, but we don’t make any guarantees about anything working on PostgreSQL older than 9.5. Although we don’t (knowingly) use any 9.5isms at present (pg_dump is doing that row_security stuff, not us), we only test on 9.5, run all of our customer sites on 9.5, and we could start using something from 9.5 at any time because it solves a problem.

2 Likes
4

And since you are on Ubuntu just use the official apt repo and remove the 9.3 and install 9.5, it’s easy :wink:

6

@Overgrow did you get it to work by upgrading postgresql?

1 Like
7

Exactly. I’ve upgraded postgres to make it work…

1 Like