The message "The database system is starting up." does not indicate an error. The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:
http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
After the rsync, did you really run what you show?:
pgsql -c "select pg_stop_backup();";
Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode. On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:
Log: consistent recovery state reached at 0/BF0000B0
and:
Log: streaming replication successfully connected to primary
Did you try connecting to the slave at this point? What happened?
The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there. I'm also concerned about these apparently conflicting statements:
The only ways I have restarted Postgres is through the service
postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands.
After I receive this error, I kill all processes and again try to
restart the database...
Did you try to stop the service through the service script? What happened? It might help in understanding the logs if you prefixed lines with more information. We use:
log_line_prefix = '[%m] %p %q<%u %d %r> '
The recovery.conf script looks odd. Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?
It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default data directory is). From there, I'd do a pg_dump on the database(s) in question and then restore them to the new server. That would probably give you the cleanest environment.
So, here are what I see as your options:
Using pg_upgrade
On The New Server (pg_upgrade)
- Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt.
- Attempt to start the service
/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/
- If that works, stop the service
/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/
- Now try your pg_upgrade script
Without Using pg_upgrade
- Create a VM or use an old, decommed server and install postgresql 8.4
- Initialize your server using
initdb (or pg_createcluster)
- Copy your backed up data over that which was just created
- Start postgresql
- Backup the data using
pg_dump
- Copy the dump file to the "new server"
- Restore to a new database on the new server
Best Answer
Without knowing your specific errors, I dont know what the easiest approach to recovering your database is. For example, there could be minor problems like bad settings that cause PostgreSQL to be unable to start. I would recommend asking a new question about getting PostgreSQL to start before looking at how to recover your data.
However, I can answer what are some aspects of your question about recovering data. I have actually never seen data corruption in PostgreSQL, and so all my experience with it is second-hand (email list threads and the like). It is very rare, and usually related to failing hardware. If you do have data corruption, my recommendation is to restore from backup onto a new server while you run diagnostics and determine the problem on your existing server.
When you talk about recovering from the files in
pg_xlogyou are talking about using write logs as incremental backups. You still need a compatible starting point, which means effectively you are going with a base backup plus all pg_xlog segments created from that point onward.Now, if you have no good backups, and you have a lot of important data, it may be possible to hire an expert for significant expense to recover some or all of your data. This is not cheap.
I think your immediate steps are to step back and ask why PostgreSQL is not starting and see what you can do about that first before assuming you need to recover your data. If you need to recover your data, you hopefully have backups.