How to Restore Postgresql Cluster from data directory

Question

in our office we have a development database server that runs Debian 5, on this server we had a postgresql 8.1 running but because of the lack of internet access from the server we backed up with pg_dump_all the entire cluster, but the dump file is lost then we installed many versions and neither 8.1 version itself recognize the database cluster, so my question is, theres a way to reinstall the same postgres 8.1 and tell him to take the old cluster from data folder? or the backup should be via the dump file only? i only have the data folder but neither the 9.2, 8.4, or the same 8.1 can recognize the cluster. this is the error im getting while trying to initialize the same cluster with pg 8.1

postgres@Desarrollo-SDQ-VE:/opt/postgres/8.1/bin$ ./postgres -D /databases/postgresql/8.1/ve/ -c config_file=/etc/postgresql/8.1/ve/postgresql.conf
[2012年11月13日 10:42:22 AST:18359id] 2012年11月13日 10:42:22 AST 2012年11月13日 10:42:22.137 AST FATAL: database files are incompatible with server
[2012年11月13日 10:42:22 AST:18359id] 2012年11月13日 10:42:22 AST 2012年11月13日 10:42:22.138 AST DETAIL: The database cluster was initialized with HAVE_INT64_TIMESTAMP but the server was compiled without HAVE_INT64_TIMESTAMP.
[2012年11月13日 10:42:22 AST:18359id] 2012年11月13日 10:42:22 AST 2012年11月13日 10:42:22.138 AST HINT: It looks like you need to recompile or initdb.

any Idea?

• Craig Ringer: That isn't the same 8.1, it's compiled with different options; possibly on a 64-bit machine instead of a 32-bit machine? Or a newer 32-bit machine with newer C libraries, etc? You really need some more specifics here: what the old OS and version was, for starters, and what the new OS and version and platform is.
• Christopher Toni Herrera Veras: Its the same machine, its a Server with a Debian 5 x64 Installation, the server is a copy of another one, i thought it was installed via apt-get but we actually don't have access to internet in this one right now, so i downloaded and compiled from source code the 8.1 version, theres a way to know all the options that apt-get use to compile by default ?
• Craig Ringer: I'd find the source package in the Debian repositories and rebuild it on an Internet connected machine, then check out the config.log. You can probably rebuild the package against your current Debian 5 too. If you're on the same OS, just "apt-get source postgresql-8.1". See debian.org/doc/manuals/apt-howto/ch-sourcehandling.en.html
• Craig Ringer: archive.debian.org/debian will be useful, particularly archive.debian.org/debian/pool/main/p/postgresql-8.1 . You want 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. Check out debian/rules in the packaging for configure options.

Answer 1

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.