What I need at this moment is a way to update the structure of the database without altering its records, I have a database that has not been touched for a long time according to the versions of the system I currently manage, therefore I need to update the structure of that database with the newest version but without altering the data in that database. The database engine I use is postgres in version 8.2
If your database uses standard table types and data types, you will not have any problems. The way is:
pgAdmin or use the pg_dump from the command line. pgAdmn . If you have any problems when doing the restore, ask again depending on the message and / or error code received
There may be problems with some function or column name, but the data is sure to be restored correctly.
Just in case, until you have the new functional version, do not erase the machine or uninstall the old posgres.
I think you are stirring two things, on the one hand is the update of the database engine, that the only way to do it is by doing the corresponding DUMP and inserting it into the new engine.
The best way to do this is to test the migration on a different machine so as not to take risks.
On the other hand you have the structure change of your tables
For this I recommend a manual process.
First Option
One way that can be done is by making separate dumps of structure and data; then modify the structure dump with the required changes and then enter the dumps in the database engine
Second Option
Another can be generating new structures from your existing tables by making CAST in the data that you require.
CREATE TABLE <table_name> AS ( SELECT <campo1>::NUMERIC AS nuevocampo1);
PortgreSQL 9.6 Manual - CREATE TABLE AS
and later when you have all your tables, create all the references and other attributes between your tables.
Third Option
Or using tools that facilitate the export and import of data, I have used this tool and it makes me fabulous