PostgreSQL Restore database from Backup

Owned by Craig Mitchell, created
Last updated: Nov 12, 2020
3 min read

 

Precis

Restoring from Backup for a PostgreSQL database happens outside of Minder. Data for Minder is held in 2 places, the SQL database (mainly for the high transaction tables like the GL) and in the *.TPS files contained within the mbs\Company directory. The TPS files are ISAM flat files and have the advantage of being very fast, the only real downside to them is a hard 2GB limit to a table, hence we need the SQL engine for those tables.

Restore the TPS Files

You can’t have users working, obviously.

  • Make sure you can do this BEFORE you need to, a quarterly practice run (onto another workstation if you like), is just good business.

  • Always make a copy of the Minder directory FIRST !

  • The Backup for the SQL database should be included with your backup media

  • it will be in the form of a text file, labeled ‘COMPANY_SQL,backup’ or similar.

  • Restore the TPS files by simply copying them from your backup media to the x:\mbs\company\ directory.

    • Do this first.

Open the Database Admin utility

  • From the Admin workstation, open the PGadmin utility & connect to the database.

 

Make a backup of the Database we are about to overwrite.

Right click on the Company, and select Backup, give it a filename.

Click on ‘Create, and then click ‘Backup’.

Once you have a backup of the existing data we can continue.

Rename the existing database.

We are not going to copy over the existing data, rather we are going to create a new database to restore into. Yes I know we just did a backup of it, but you can never ever have too many backups!

Right click on the database again, this time going to ‘Properties’

 

Rename the database as xxx_old (or similar), click on save. This is a good test to see if the database is in-active, it won’t allow you to rename if there are active connections.

Create the New database to receive the restored data.

Right click on Databases, Create, Database

Give it the same name as the replaced database (Caps are important)

Owner is ‘minder’ .

 

Add an appropriate comment & Save. This will create the new Database.

Restore the Data

6. Right click on the New company database & select ‘Restore’

Find your file & enter it into Filename

Click on Restore and the data will be uploaded to the new database. (no need to change any options, the defaults are fine.)

Wait a minute or 2, a dialog box will indicate when the restore is complete.

The restore from backup is now complete.

 

Ensure that the new database name matches exactly the details in the Company section of Minder, caps are important here.

 

 

 

 

Copyright Programmed Network Management PL 2023