Open version 9.3 .out file with version 12

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Open version 9.3 .out file with version 12

Killian Driscoll
I have a backup of a small database last used in 2016, with the backup saved as an .out file and .sql file in version 9.3. I no longer have PostgreSQL installed. I want to access the database again, and see that version 9.3 is not supported. Can I download version 12 and restore the file, or should I install version 9.3?

Regards,

Killian DriscoIl

Reply | Threaded
Open this post in threaded view
|

Re: Open version 9.3 .out file with version 12

Keith


On Tue, Sep 15, 2020 at 11:00 AM Killian Driscoll <[hidden email]> wrote:
I have a backup of a small database last used in 2016, with the backup saved as an .out file and .sql file in version 9.3. I no longer have PostgreSQL installed. I want to access the database again, and see that version 9.3 is not supported. Can I download version 12 and restore the file, or should I install version 9.3?

Regards,

Killian DriscoIl


pg_restore does its best to be backwards compatible to try and load old dump files. So I would just try it and see with PG12.

However, there is a chance, depending on what was in that old version, that it may not restore without error. In that case, you could bring up a 9.3 cluster and restore the dump there. Then use the pg_dump binary from PG12 to create a new dump. The newer pg_dump binaries running on old servers try and convert things as needed.

Reply | Threaded
Open this post in threaded view
|

Re: Open version 9.3 .out file with version 12

Keith


On Tue, Sep 15, 2020 at 12:21 PM Keith <[hidden email]> wrote:


On Tue, Sep 15, 2020 at 12:14 PM Killian Driscoll <[hidden email]> wrote:
Thanks.

If I enter:
Server [localhost]:  pg_restore --dbname=project_28_3_17 -h localhost -p 543 2 -U postgres --create --verbose C:\ project_28_3_17.out

It then says "Database [postgres]:" in the command line, but nothing appears to have happened.

Regards,
Killian DriscoIl


Not sure if it's a copy-n-paste issue, but you have a space in the port number (543 2   vs   5432)

It would've thought it would've given an error for that tho. I'm not sure what that prompt means. I don't run PostgreSQL on windows myself.



Just realized, are you running pg_restore while logged into psql? The pg_restore command is an independent command that gets run outside of psql.



Also, please keep the mailing list in the CC so that others that come across this in the future know the answers that have been given.


 

On Tue, 15 Sep 2020 at 17:58, Keith <[hidden email]> wrote:


On Tue, Sep 15, 2020 at 11:40 AM Killian Driscoll <[hidden email]> wrote:
Thanks.
I am using Windows 7. I have installed 9.3, and have the SQL Shell (psql) open, and it says "Server [localhost]:" Do I enter the commands there? I have the .out file in my C drive, but do I need to move it to a particular PostgreSQL folder for it to restore?(Please bear with me, it's years since I did this...).

Regards,
Killian DriscoIl




On Tue, 15 Sep 2020 at 17:32, Keith <[hidden email]> wrote:


On Tue, Sep 15, 2020 at 11:00 AM Killian Driscoll <[hidden email]> wrote:
I have a backup of a small database last used in 2016, with the backup saved as an .out file and .sql file in version 9.3. I no longer have PostgreSQL installed. I want to access the database again, and see that version 9.3 is not supported. Can I download version 12 and restore the file, or should I install version 9.3?

Regards,

Killian DriscoIl


pg_restore does its best to be backwards compatible to try and load old dump files. So I would just try it and see with PG12.

However, there is a chance, depending on what was in that old version, that it may not restore without error. In that case, you could bring up a 9.3 cluster and restore the dump there. Then use the pg_dump binary from PG12 to create a new dump. The newer pg_dump binaries running on old servers try and convert things as needed.


Dump backups of PostgreSQL are either one giant, plaintext sql file that contains all roles and databases in the old cluster (generated by pg_dumpall). Or there are two or more files, one plaintext that contains just global info (roles & tablespaces) and one or more database dumps (typically binary dumps done with pg_dump -Fc).

Plain sql files must be restored with psql (or some other program that can read plaintext sql). You can feed an sql file to the psql command with the -f option. It doesn't need to be in any particular place as long as you give the proper path to -f

Binary dump files (assuming that's what the .out file was) must be restored with pg_restore. Binary pg_dump files only contain a single database and do not contain role information. So if the roles it's expecting to run GRANT commands on don't exist, you may get errors. So typically there's a plaintext sql file with the roles you should be restoring first if you are doing a full restore to a clean instance.