Обсуждение: Can't take base back up with Postgres 9.0 on Solaris 10
Hi everyone, I'm trying to take a base backup of my primary DB ready for copying to my standby. I've followed the instructions to configure my postgresql.conf at hxxp://www.postgresql.org/docs/9.0/static/continuous-archiving.html, but I get one of two errors depending on how I try to run the backup: bash-3.00$ bin/psql -c "SELECT pg_start_backup('basebackup', true)" ERROR: function pg_start_backup("unknown", boolean) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. bash-3.00$ bin/psql -c "SELECT pg_start_backup('basebackup')" ERROR: WAL archiving is not active HINT: archive_command must be defined before online backups can be made safely. Here are the lines I've changed in postgresql.conf: listen_addresses = '*' # what IP address(es) to listen on; port = 5555 # (change requires restart) wal_level = archive # minimal, archive, or hot_standby archive_mode = on # allows archiving to be done archive_command = 'scp %p postgres@172.18.131.211:/postgres/postgres/9.0-pgdg/primary_archive' # command to use to archive a logfile segment max_wal_senders = 1 # max number of walsender processes wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables Could anyone tell me what the problem might be? Thanks, Dan -- View this message in context: http://postgresql.1045698.n5.nabble.com/Can-t-take-base-back-up-with-Postgres-9-0-on-Solaris-10-tp3240364p3240364.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 28 October 2010 12:42, dan.m.harris <daniel.harris@metaswitch.com> wrote: > > Hi everyone, > > I'm trying to take a base backup of my primary DB ready for copying to my > standby. I've followed the instructions to configure my postgresql.conf at > hxxp://www.postgresql.org/docs/9.0/static/continuous-archiving.html, but I > get one of two errors depending on how I try to run the backup: > > bash-3.00$ bin/psql -c "SELECT pg_start_backup('basebackup', true)" > ERROR: function pg_start_backup("unknown", boolean) does not exist > HINT: No function matches the given name and argument types. You may need to > add explicit type casts. > bash-3.00$ bin/psql -c "SELECT pg_start_backup('basebackup')" > ERROR: WAL archiving is not active > HINT: archive_command must be defined before online backups can be made > safely. > > Here are the lines I've changed in postgresql.conf: > listen_addresses = '*' # what IP address(es) to listen on; > port = 5555 # (change requires restart) > wal_level = archive # minimal, archive, or hot_standby > archive_mode = on # allows archiving to be done > archive_command = 'scp %p > postgres@172.18.131.211:/postgres/postgres/9.0-pgdg/primary_archive' # > command to use to archive a logfile segment > max_wal_senders = 1 # max number of walsender processes > wal_keep_segments = 32 # in logfile segments, 16MB each; 0 disables Go into psql and type "SHOW wal_level;" What does it say? If it doesn't say "archive" then you've either not restarted PostgreSQL since you changed it, or you're looking at the wrong config file. As for the first error, have you tried just typing that command whilst in psql rather than passing it as a -c parameter? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Hi Thom, thanks for your reply. Here's my psql output: bash-3.00$ bin/psql psql (9.0.1, server 8.1.4) WARNING: psql version 9.0, server version 8.1. Some psql features might not work. Type "help" for help. postgres=# SHOW wal_level; ERROR: unrecognized configuration parameter "wal_level" postgres=# SELECT pg_start_backup('basebackup', true); ERROR: function pg_start_backup("unknown", boolean) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. postgres=# SELECT pg_start_backup('basebackup'); ERROR: WAL archiving is not active HINT: archive_command must be defined before online backups can be made safely. The mismatched server version number doesn't look right! Note that this is a Postgres 9.0 tarball I've extracted onto a server which already has Postgres 8.1.4 installed. Any thoughts from anybody? Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Can-t-take-base-back-up-with-Postgres-9-0-on-Solaris-10-tp3240364p3240406.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 28 October 2010 13:07, dan.m.harris <daniel.harris@metaswitch.com> wrote: > > Hi Thom, thanks for your reply. Here's my psql output: > > bash-3.00$ bin/psql > psql (9.0.1, server 8.1.4) > WARNING: psql version 9.0, server version 8.1. > Some psql features might not work. > Type "help" for help. > > postgres=# SHOW wal_level; > ERROR: unrecognized configuration parameter "wal_level" It's not recognised because your 9.0.1 psql client is connecting to a 8.1.4 server which doesn't use such a parameter, hence the warning you're receiving when you run psql. This also explains why you can't use the 2-parameter form of pg_start_backup as that wasn't introduced until 8.4. You need to connect to a 9.0 instance. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Thursday 28 October 2010 5:07:06 am dan.m.harris wrote: > Hi Thom, thanks for your reply. Here's my psql output: > > bash-3.00$ bin/psql > psql (9.0.1, server 8.1.4) > WARNING: psql version 9.0, server version 8.1. > Some psql features might not work. > Type "help" for help. > > postgres=# SHOW wal_level; > ERROR: unrecognized configuration parameter "wal_level" > postgres=# SELECT pg_start_backup('basebackup', true); > ERROR: function pg_start_backup("unknown", boolean) does not exist > HINT: No function matches the given name and argument types. You may need > to add explicit type casts. > postgres=# SELECT pg_start_backup('basebackup'); > ERROR: WAL archiving is not active > HINT: archive_command must be defined before online backups can be made > safely. > > > The mismatched server version number doesn't look right! Note that this is > a Postgres 9.0 tarball I've extracted onto a server which already has > Postgres 8.1.4 installed. > Did you use the source version of 9.0 and do configure/make to install it? If so you might want to take a look at: http://www.postgresql.org/docs/9.0/interactive/install-upgrading.html Going from a major versions (8.1 to 9.0 in this case) requires some additional steps. Besides the above problem you ran into, there will probably others even if you follow the upgrade procedure. There have been a lot of changes from 8.1 to 9.0, not the least of which is the dropping of implied type casts in 8.3. You probably want to spend some time with the Release Notes: http://www.postgresql.org/docs/9.0/interactive/release.html > Any thoughts from anybody? Thanks. > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Can-t-take-base-back-up-with-Postgr >es-9-0-on-Solaris-10-tp3240364p3240406.html Sent from the PostgreSQL - > general mailing list archive at Nabble.com. -- Adrian Klaver adrian.klaver@gmail.com
I was just being a bit dull, I'm afraid - I've had a read of the psql help, and by default it connects to port 5432, which is the port that's listening for the PG 8.1.4 install on my server. I ran 'bin/psql -p 5555' instead, to point at my PG 9.0 instance, and the pg_start_backup worked fine. Thanks to all! Dan -- View this message in context: http://postgresql.1045698.n5.nabble.com/Can-t-take-base-back-up-with-Postgres-9-0-on-Solaris-10-tp3240364p3240641.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thursday 28 October 2010 5:07:06 am dan.m.harris wrote: > Hi Thom, thanks for your reply. Here's my psql output: > > bash-3.00$ bin/psql > psql (9.0.1, server 8.1.4) > WARNING: psql version 9.0, server version 8.1. > Some psql features might not work. > Type "help" for help. > > postgres=# SHOW wal_level; > ERROR: unrecognized configuration parameter "wal_level" > postgres=# SELECT pg_start_backup('basebackup', true); > ERROR: function pg_start_backup("unknown", boolean) does not exist > HINT: No function matches the given name and argument types. You may need > to add explicit type casts. > postgres=# SELECT pg_start_backup('basebackup'); > ERROR: WAL archiving is not active > HINT: archive_command must be defined before online backups can be made > safely. > > > The mismatched server version number doesn't look right! Note that this is > a Postgres 9.0 tarball I've extracted onto a server which already has > Postgres 8.1.4 installed. > > Any thoughts from anybody? Thanks. > In my previous response I was assuming you where replacing one version with the other. More brain cells have woken up and I realized you might also be talking about running two instances on the same machine at the same time. That is possible but you will need to have each instance run on a different port in postgresql.conf (port=? where 5432 is the default). You will also need to make sure you are using the right program with the right cluster. Currently when you run psql you are calling the one installed with 9.0.1 which is connecting to the 8.1.4 cluster, which at a guess is probably running on port 5432. You have the choice of either specifying full paths to the appropriate binary or creating sym links. -- Adrian Klaver adrian.klaver@gmail.com