How to Restore Old Data to New Version of PostgreSQL

Discover how to effectively restore your databases from older PostgreSQL versions to a new installation. Follow our comprehensive guide for seamless data migration!
---
This video is based on the question https://stackoverflow.com/q/66368589/ asked by the user 'Sapna Sharma' ( https://stackoverflow.com/u/9362919/ ) and on the answer https://stackoverflow.com/a/66369095/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Restore old data to new version of Postgres

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Restore Old Data to New Version of PostgreSQL

When it comes to upgrading your PostgreSQL database, you may find yourself facing an important question: How can you transfer your old data to a new version effectively? This process can be tricky, especially if you are jumping from a much older version like PostgreSQL 9.3 to a newer version such as PostgreSQL 12. Fortunately, we have a clear and comprehensive guide to help you tackle this challenge smoothly.

The Issue at Hand

You have recently installed PostgreSQL 12 on your new server and transferred the raw data files from your old server running PostgreSQL 9.3. However, when you attempt to list the databases using the command /l, you don't see any of your old databases. This can be frustrating, but there is a specific method to restore your databases correctly.

Why the Direct Transfer Doesn't Work

The reason you can't see your databases after copying the raw data files is that simply transferring these files does not automatically convert them to be compatible with the newer version of PostgreSQL. Each version of PostgreSQL has specific formats and structures for its data storage, and direct file copying won't bridge that gap.

Steps to Restore Your Databases

To successfully restore your databases from an older PostgreSQL version to a new one, follow these steps:

Step 1: Start the Old Version of PostgreSQL

Install PostgreSQL 9.3 on the New Server:
Ensure that you have PostgreSQL 9.3 installed on your new server. This will allow you to run the necessary commands on the data formatted for that version.

Start the PostgreSQL 9.3 Service:
Use the command line to start the PostgreSQL 9.3 service so that you can perform the next steps.

Step 2: Use pg_dumpall to Extract the Data

Run pg_dumpall:
With the older version of PostgreSQL running, you can now extract all of your databases using:

[[See Video to Reveal this Text or Code Snippet]]

This command will create a dump file named all_databases.sql containing all the data and schema information from your 9.3 databases.

Use the Version 12 pg_dumpall Utility:
It is recommended to run the pg_dumpall command from your PostgreSQL 12 installation. This ensures that any particular features or changes in version 12 are properly accounted for in the dump.

Step 3: Restore the Data to the New Server

Stop the PostgreSQL 9.3 Service:
After you have created the dump file, stop the PostgreSQL 9.3 service.

Start PostgreSQL 12:
Now, start your PostgreSQL 12 service.

Restore the Dump File:
Use the following command to restore the data into your new PostgreSQL 12 installation:

[[See Video to Reveal this Text or Code Snippet]]

This command will execute the dump script and rebuild your databases in the PostgreSQL 12 environment.

Step 4: Verify the Restoration

Check for Databases:
After the restoration is complete, you can use the command:

[[See Video to Reveal this Text or Code Snippet]]

This will list all your databases, confirming that the data has been successfully restored.

Conclusion

Upgrading and restoring PostgreSQL databases can seem daunting, but by following these straightforward steps, you can migrate your data with ease. Always remember to use the appropriate tools for the specific version of PostgreSQL you are working with. If you take these steps diligently, you'll successfully relocate your databases to their new version without a hitch!

For those facing similar challenges, feel free to share your experiences in the comments below!