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?
I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.
Every cluster uses three directories:
- /etc/postgresql/8.4/CLUSTERNAME, for its configuration
- your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
- your log directory, for your logs (default /var/log/postgresql)
Best Answer
You could always double check your problem by issuing a command such as
/usr/bin/pgsql/bin/createdb test
You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III.
But, in order to change a system parameter, you have to reload the
postgresql.conf
file after making changes. You can do this by eitherrebooting the server or
reloading the .conf file or
change on the client side
From here, some parameters always need a reboot and some can be dynamically changed by simply reloading.
I tried to find a clear list of those which required reboot and which required reload, but failed (grateful for input on this!).
For a system reboot,
pgctl restart
To reload, you could also try these commands (from here - or check the documentation page given above).
Option 1: From the command-line shell
Option 2: Using SQL
You can verify that the setting has "taken" from the table pg_settings. Also of interest is this page.
For the
default_transaction_read_only
parameter, you can also set it directly from the client (see here). Check the links here for how to set all parameters - reboot, reload or client.