0

I am working on a PostgreSQL database and recently we had a server upgrade, during which we changed our drive from a 2Tb raid Hard disk to a SSD. Now I mounted the RAID drive on a partition and can even access it.

Next what I would like to do is to get the database out of the mounted drive and restore it on the currently running PostgreSQL. How can I achieve this?

root@check03:/mnt/var/lib/postgresql/9.1/main/global# ls
11672      11674  11805  11809      11811  11813_fsm  11816  11820  11822  11824_fsm  11828  11916  11920        pg_internal.init
11672_fsm  11675  11807  11809_fsm  11812  11813_vm   11818  11820_fsm  11823  11824_vm   11829  11918  pg_control   pgstat.stat
11672_vm   11803  11808  11809_vm   11813  11815      11819  11820_vm   11824  11826      11914  11919  pg_filenode.map
root@check03:/mnt/var/lib/postgresql/9.1/main/global# cd ..

As you can see I am able to access the drives and the folders, but I don't know what to do next. Kindly let me know. Thanks a lot.

Improve this question

2 Answers 2

Reset to default
2

You need the same version of PostgreSQL (9.1), also the same or later minor version. copy main/ and everything below that to the new location. Copy the configuration of the old instance and adapt the paths to fit to the new location (the main/ ist the ''data directory'' (also sometimes called PGDATA)). Start the new instance and look carefully at the logs. You should probably rebuild any indexes.

Also read about the file layout in the fine documentation.

EDIT: If you have any chance to run the old configuration, read about backup and restore, this is a much more safe way to transfer data.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It is the same version of PostgreSQL as the admin just took an image of the 2.0TB hd and put it in the SSD. I am taking a backup of main directory from the SSD first and planning to restore it with the the main folder from the 2.0TB hard disk. I presume that is correct.
1
  • the Postgres binaries must be the same version
  • make sure that postgres is not running
  • copy using cp -rfp or tar | tar or cpio , or whatever you like. Make sure you preserve the file owners and mode (top-level-directory must be 0700, owned by postgres)
  • make sure that the postgres-startup (in /etc/init.d/postxxx) refers to the new directory; sometimes there is an environment variable $PGDATA contiaining the name of the postgres data directory; maybe you need to make changes to new_directory/postgres.conf, too (pg_log et al)
  • for safety, rename the old data directory
  • restart Postgres
  • try to connect to it; check the logs.

Extra:

  • Seasoned unix-administrators (like the BOFH ;-) might want to juggle with mountpoints and/or symlinks (instead of copying). Be my guest. YMMV
  • Seasoned DBAs might want to create a tablespace, point it at the new location and (selectively) move databases, schemas or tables to the new location.
Improve this answer

5 Comments

I used cp -R and copied the main directory itself. I am getting error incomplete startup packet. What is the problem?
Either your copy was rotten or incomplete, or you copied the hot database files. Did you shutdown before copying?
I solved it by copying one file which was missing. I will accept your answer as I used cp -rfp, but before that, cp -rfp does not create any soft-links from the source to destination, right?
LOG: incomplete startup packet is not an error, it's an inconsequential log. Otherwise you'd see ERROR or FATAL instead of LOG.
It worked already, can you please answer the question I have asked in my comment before.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.