Postgresql – How to solve an error code 127 when doing a pg_dumpall through a cronjob

backuppostgresql

I have this simple script to backup all my databases in postgres

    pg_dumpall -U pg_user > /tmp/tmp.txt
    DUMP_STATUS=$?
    echo "Dump status: $DUMP_STATUS" > /tmp/status.txt

The dump status is 0 and the tmp.txt file is properly created when the script is executed from the command line.

However, if I execute this script as a cronjob in my crontab, my tmp.txt is empty (the dump of all databases failed) and my status.txt file contains a dump status 127.

A strange behavior I noticed is that pg_dumpall will pipe the information into the file but also print it on the terminal. I'm thinking this behavior might be the cause of the problem.

Any idea how I can solve this issue?

I am running FreeBSD:

    > uname -mrs  
    FreeBSD 9.1-PRERELEASE amd64

    > psql -V
    psql (PostgreSQL) 9.1.7
    contains support for command-line editing

Best Answer

If the script can work apart from a crontab, you should make sure of the following:

The script file contains the necessary environmental variables
- $PGHOME
- $PGDATA
- $PGPORT
- $PATH
Output the standard error to a file for future analysis

Related Solutions

Postgresql – Upgrade Postgres without doing a pg_dumpall

Since 8.4, pg_upgrade is the alternate choice to dump-restore for upgrades between major versions. It is actually linked at the end of the doc page on migration you already mentioned.

As for replaying WAL files from an older version, the doc says we can't:

In general, log shipping between servers running different major PostgreSQL release levels is not possible

(in Log-Shipping Standby Servers).

Postgresql – How to solve ‘tuple concurrently updated’ error

I manage to solve my problem by changing my query to this one:

INSERT INTO my_stat(id, client_addr, pid, usename)
    SELECT nextval('mystat_sequence'), client_addr, pid, usename
    FROM (
        SELECT
           host(client_addr) as client_addr,
           pid ,
           usename
        FROM pg_stat_activity
        WHERE usename = current_user
    ) s
    WHERE
        NOT EXISTS (
           SELECT NULL
           FROM my_stat u
           WHERE current_date = u.creation
               AND s.pid = u.pid
               AND s.client_addr = u.client_addr
               AND s.usename = u.usename
        );

I think something happened under the hood right from the Postgresql internals but I can't figure out what ...

Best Answer

Related Solutions

Postgresql – Upgrade Postgres without doing a pg_dumpall

Postgresql – How to solve ‘tuple concurrently updated’ error

Related Question