Re: Open version 9.3 .out file with version 12 - Mailing list pgsql-novice

On Tue, Sep 15, 2020 at 12:14 PM Killian Driscoll <killiandriscoll@gmail.com> 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.

 

On Tue, 15 Sep 2020 at 17:58, Keith <keith@keithf4.com> wrote:

On Tue, Sep 15, 2020 at 11:40 AM Killian Driscoll <killiandriscoll@gmail.com> 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 <keith@keithf4.com> wrote:

On Tue, Sep 15, 2020 at 11:00 AM Killian Driscoll <killiandriscoll@gmail.com> 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.