Postgresql – Repopulating PostgreSQL database with data directory from another database Instance

postgresql

I have had to delete PostgreSQL for an issue and reinstall it, however before uninstalling I did not execute the command pg_dump. I still have the data directory from this instance. After reinstalling PostgreSQL, I receive all the same tables and schemas from my prior database, but not the same data. The data directories are two separate, but I want to combine the previous data directory into the new one. How would I go about such a process, if possible?

Best Answer

Assumptions

I'm assuming you have two instances each one with a different data directory but with the same schema for the same version of postgres installed. I'm also assuming when the server starts is using your newly created, possibly empty, instance

What to do

You should try to start a new instance of the server pointing to your backup data directory...
Somenthing like

pg_ctl -D /path/to/backup/dir -l /path/to/log start -o "-p 5433"

If this works, your backup instance is running on port 5433.
Check if everything is in place and the data is consistent.

If so then you could simply make arrangements for your server to always start pointing to your backup directory and safely delete your newly created data directory.

Related Solutions

PostgreSQL: Trouble accessing another server’s Data via NFS

I figured out the issue. It was due to improperly shared NAS folder. I fixed it by using this entry in /etc/exports

/var/lib/postgresql/ 192.168.1.0/24(rw,nohide,insecure,no_subtree_check,async)

to share.

And if it interests anybody, I managed to get shared database running successfully. The only folder that needed to be common was the above folder. And both PostgreSQL Server instances need to have identical configuration files for the data folder to be shared successfully.

Postgresql – How to Restore Postgresql Cluster from data directory

Here's what I'd do:

Download postgresql-8.1_8.1.8-1.diff.gz, postgresql-8.1_8.1.8-1.dsc and postgresql-8.1_8.1.8.orig.tar.gz from http://archive.debian.org/debian/pool/main/p/postgresql-8.1/ on a machine running the same OS as what you intend to install on.
dpkg-source -x postgresql-8.1_8.1.8-1.dsc
Examine debian/rules for configure options.

This shows:

DEB_CONFIGURE_EXTRA_FLAGS := --mandir=\$${prefix}/share/postgresql/8.1/man \
                    --with-docdir=\$${prefix}/share/doc/postgresql-doc-8.1 \
                    --datadir=\$${prefix}/share/postgresql/8.1 \
                    --bindir=\$${prefix}/lib/postgresql/8.1/bin \
                    --includedir=\$${prefix}/include/postgresql/ \
                    --enable-nls \
                    --enable-integer-datetimes \
                    --enable-thread-safety \
                    --enable-debug \
                    --disable-rpath \
                    --with-tcl \
                    --with-perl \
                    --with-python \
                    --with-pam \
                    --with-krb5 \
                    --with-openssl \
                    --with-gnu-ld \
                    --with-tclconfig=/usr/lib/tcl$(TCL_VER) \
                    --with-tkconfig=/usr/lib/tk$(TCL_VER) \
                    --with-includes=/usr/include/tcl$(TCL_VER) \
                    --with-pgport=5432 \
                    $(ARCH_OPTS) \
                    CFLAGS='$(CFLAGS)' \
                    LDFLAGS='$(LDFLAGS)'

This should help you do your own source build. In particular I think you probably need --enable-integer-datetimes.

Alternately, you could dpkg-buildpackage -rfakeroot -uc -b to build the package, then copy it to the target server and install it.