3

I've started a new SLAVE PostgreSQL server set up.

master: 192.168.100.1

slave1: 192.168.100.2

slave2(NEW SLAVE) 192.168.100.3

* NOTE: I run the pg_basebackup from another STANDBY SERVER. Not from the MASTER

1 - screen -t basebackup

2 - su - postgres

3 - cd ~/9.2/data/

4 - ssh postgres@slave1 'pg_basebackup --pgdata=- --format=tar --label=bb_master --progress --host=localhost --port=5432 --username=replicator --xlog | pv --quiet --rate-limit 100M' | tar -x --no-same-owner

5 - I've commented the "primary_conninfo =" and "standby_mode=" so the slave can get the files from WAL_ARCHIVE

6 - Afte I got the logs:

postgres(iostreams)[10037]:       2016-01-09 00:07:26.604 UTC|10085|LOG:  database system is ready to accept read only connections

7 - After the server finished the WAL_ARCHIVE, I turned on replication from MASTER on recovery.conf:

recovery.conf on the New Slave:

restore_command = 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/restore_wal_segment.bash "../wal_archive/%f" "%p"'
archive_cleanup_command = 'exec nice -n 19 ionice -c 2 -n 7 ../../bin/pg_archivecleaup_mv.bash -d "../wal_archive" "%r"'
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.100.2 port=5432 user=replicator application_name=replication_slave02'

But, once I've restarted the POSTGRESQL I got this error:

WAL segment `../wal_archive/00000005.history` not found
2016-01-09 01:13:39.183 UTC|774|FATAL:  timeline 2 of the primary does not match recovery target timeline 4

ls /var/lib/pgsql/9.2/wal_archive

postgres postgres 0000000200000C6900000065
postgres postgres 0000000200000C6900000066

restore_wal_segment.bash:

#!/bin/bash -eu


# multi version switch for the pg_archivecleanup tool
# package postgresql92-contrib doesn't create an /etc/alernatives link for pg_archivecleanup, and probably
# it's not desirable either. This command switches between different versions of the command based on the
# path it's been invoked from


declare -r -x PATH='/usr/local/bin:/usr/bin:/bin';




declare -i TS_THRESHOLD=0;
declare -i WAL_MTIME=-1;

declare PG_VERSION='';



    # invocation check
    if [ ${#} -lt 2 ] || ([ ${#} -ge 3 ] && (! [[ "${3}" =~ ^[0-9]{1,10}$ ]])); then
        printf 'Usage:\n' 1>&2;
        printf '\t%s { wal_segment_file } { target_file } [ minimum_age_in_seconds ]\n' "${0}" 1>&2;
        exit 2;
    fi;



    # make sure we're running in a PG cluster and extract the version
    if \
        [ -f 'PG_VERSION' ] && \
        [ -f 'postmaster.opts' ] && \
        [ -a 'pg_xlog' ] \
    ; then
        PG_VERSION="$(0<'PG_VERSION')";
        if (! [[ "${PG_VERSION}" =~ ^[0-9]+\. ]]); then
            printf 'PG cluster advertises an invalid PostgreSQL version (%s)\n' "${PG_VERSION}" 1>&2;
            exit 3;
        fi;
    else
        printf 'Current path does not look like a PostgreSQL cluster directory. Aborting\n' 1>&2;
        exit 3;
    fi;

    # This is race-condition prone but we're really trusting postgres not to start multiple instances of this
    # routine in parallel
    if (! ([ -f "${1}" ] || [ -h "${1}" ])); then
        printf 'WAL segment `%s` not found\n' "${1}" 1>&2
        exit 4;
    fi;

    if [ ${#} -ge 3 ]; then
        # we refuse to restore files newer than the threshold with error 7
        TS_THRESHOLD=$(($(date +'%s') - ${3}));
        WAL_MTIME=$(stat --format='%Y' "${1}");

        if [ ${WAL_MTIME} -gt ${TS_THRESHOLD} ]; then
            printf 'Archived WAL segment `%s` is newer than the configured delay (%d seconds)\n' "${1}" ${3} 1>&2
            exit 7;
        fi;
    fi;

    exec cat 0<${1} 1>"${2}";

pg_archivecleaup_mv.bash

#!/bin/bash -eu


# multi version switch for the pg_archivecleanup tool
# package postgresql92-contrib doesn't create an /etc/alernatives link for pg_archivecleanup, and probably
# it's not desirable either. This command switches between different versions of the command based on the
# path it's been invoked from


declare -r -x PATH='/usr/local/bin:/usr/bin:/bin';


declare PG_VERSION='';


    # make sure we're running in a PG cluster and extract the version
    if \
        [ -f 'PG_VERSION' ] && \
        [ -f 'postmaster.opts' ] && \
        [ -a 'pg_xlog' ] \
    ; then
        PG_VERSION="$(0<'PG_VERSION')";
        if (! [[ "${PG_VERSION}" =~ ^[0-9]+\. ]]); then
            printf 'PG cluster advertises an invalid PostgreSQL version (%s)\n' "${PG_VERSION}" 1>&2;
            exit 3;
        fi;
    else
        printf 'Current path does not look like a PostgreSQL cluster directory. Aborting\n' 1>&2;
        exit 3;
    fi;

    # we got the version. we just try to run it. -u is unset so that we can pass "${@}" even if it's not set
    set +u;
    exec "/usr/pgsql-${PG_VERSION}/bin/pg_archivecleanup" "${@}";

What can I do to solve the problem? It's really important as it's a production New Slave. Thank you!

  • can you verify if the files exist in the locations show in the error messages? And that the postgres process has access to them? – Max Vernon Jan 9 '16 at 0:32
  • 1
    There is no file like 00000005.history and 0000000400000C68000000C8 on MASTER or on the new Slave – Lucas Jan 9 '16 at 0:35
  • I've found the 0000000400000C68000000C8 but not the 00000005.history – Lucas Jan 9 '16 at 0:51
  • I have a feeling you've run into the limitations that are mentioned at postgresql.org/docs/9.2/static/app-pgbasebackup.html – dezso Jan 9 '16 at 22:29
  • must be an "answer".some way to get this solved... – Lucas Jan 10 '16 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.