The default directory where PostgreSQL keeps configuration file is: C:\Program Files\PostgreSQL \X.X\data). Two config files ( nf and pg_hba.conf) must be have a backup taken as the newer installation will replace the new config file with default configuration parameters and values. Generally, backing up of data is not essential as current PostgreSQL 9.x will not get affected during or after the update using the pg_upgrade process. The PostgreSQL Config file and user database file must be backed up using pg_dump command before initiating the PostgreSQL upgrade process. We will cover many requirements in-depth in this text to eradicate the numerous dynamic errors and challenges. Upgrading the PostgreSQL server can be done by installing the newer version of Postgres alongside the current one and executing the pg_upgrade command with essential parameters. A manual backup and restore process is not required when we use the pg_upgrade as the command will automatically copy the data directory to the newer version. Don't keep on using your obsolete database version.This text will elaborate on upgrading the PostgreSQL database from the 9.x version to the 12.x version. If you can, follow the advice above to make a dump. Try to connect with psql and see if you can access your data. Also check the logs in the pg_log directory of the datadir and make sure everything looks sensible, there are no errors there. If it didn't, look in the Event Viewer for info on why. Start the PostgreSQL service from services.mscĬheck to make sure the service started correctly. This is important the database won't work without correct permissions and the unzipped copy won't have the correct permissions. In the security tab, give the "postgres" user full control and apply that to all files and folders within. You might actually have to unzip it somewhere else then move it into place if you're running a non-admin or a UAC admin account. Unzip your old datadir to the location you just moved the installer-created datadir from, so it has the same name and location. I think that's C:\Program Files\PostgreSQL\8.2\data for 8.2, but I can't remember for sure. Rename the data directory to something like data-empty. Once you have 8.2 installed and you know it's working with the empty database created by the installer, shut it down from the services control panel (start -> run -> services.msc) Because it's such an old version it may not co-exist with current versions already installed, so you might have to use a spare machine or a virtual machine. No other version will work for this purpose, as PostgreSQL raw data files are only compatible within the same major release. I'd already written it up when I saw you had the binaries in the zip file and I'll keep it here for others. Here's what you'd do if you didn't have the PostgreSQL binaries. Now promptly fix your backup procedures to schedule a daily pg_dump so this never happens to you again This may require some changes to your code see the release notes for each major. Please do not try to keep on running 8.2, which is an obsolete and unsupported release. Copying just pg_dump.exe alone will not work as it requires libpq and the other libraries. You can copy pg_dump from a new machine to the old machine if you'd prefer not to set up to dump over the network just copy the whole `bin' dir from a new PostgreSQL release to somewhere on the old machine and run pg_dump from there. If you have problems restoring to newer releases like 9.0 or 9.1 you might have to use pg_dump from the newer release to connect to the old database and make the dump, rather than using pg_dump from 8.2 to make the dump. and restore that to a modern PostgreSQL release using pg_restore from the current release. ![]() bin\pg_dump.exe -Fc -U mydbusername -f mydb.dbbackup mydb.If psql connects OK disconnect with the \q command then promptly make a dump of the database: I'll assume it's on your desktop: C:\Users\Myusername\Desktop\postgresql - for the purpose of this example.Īssuming that the c:\users\myusername\desktop\postgresql folder contains the folders named bin, data, lib, etc:īin\pg_ctl.exe start -D C:\users\myusername\desktop\postgresql\data Unzip your backup dir somewhere convenient where you have full read/write permission as your normal user account. ![]() ![]() Bad office people, no cookie.īecause you have the binaries from the same release, you can save some hassle. Ideally you would've done a dump with pg_dump, which would've made this much easier.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |