Re: Several problems with installation on FreeBSD - how to reinstall without loosing data - Mailing list pgsql-novice
| From | Jon Theil Nielsen |
|---|---|
| Subject | Re: Several problems with installation on FreeBSD - how to reinstall without loosing data |
| Date | |
| Msg-id | CABL6qKeen+1xes7EhaHOqHEKnXCGJReyOnz9+hasjVzkPv1nAQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Several problems with installation on FreeBSD - how to reinstall without loosing data (Kevin Grittner <kgrittn@ymail.com>) |
| Responses |
Re: Several problems with installation on FreeBSD - how to
reinstall without loosing data
|
| List | pgsql-novice |
2013/9/18 Kevin Grittner <kgrittn@ymail.com>
Jayadevan M <maymala.jayadevan@gmail.com> wrote:... keeping in mind that many utilities will default user ID and
> You have lines in the output which say
> "Sep 17 17:39:43 <local5.info> mflserver4 postgres[21022]: [39-1] LOG: connection authorized: user=root database=root"
> In case you are not doing it manually, do you have some cron job
> that is expected to backup the database or vacuum the database,
> and it is trying to connect to the database as root user, to
> database root?
database name to the OS user if not specified on the command line.
Also, pg_ctl will attempt to connect to the database on a start or
restart to confirm that it is running.
Once again: Thanks for the proposals.
I am not quite sure if files in /usr/local/etc/periodic/ are run, but I guess so. The command grep -ir sql . in this directory shows:
./daily/502.pgsql:# $FreeBSD: /tmp/pcvs/ports/databases/postgresql92-server/files/502.pgsql.in,v 1.4 2011-10-18 09:03:32 girgen Exp $
./daily/502.pgsql:# daily_pgsql_backup_enable="YES" # do backup of all databases
./daily/502.pgsql:# daily_pgsql_backup_enable="foo bar db1 db2" # only do backup of a limited selection of databases
./daily/502.pgsql:# daily_pgsql_vacuum_enable="YES" # do vacuum
./daily/502.pgsql:daily_pgsql_user=pgsql
./daily/502.pgsql:daily_pgsql_vacuum_args="-U ${daily_pgsql_user} -qaz"
./daily/502.pgsql:daily_pgsql_pgdump_args="-U ${daily_pgsql_user} -bF c"
./daily/502.pgsql:daily_pgsql_pgdumpall_globals_args="-U ${daily_pgsql_user}"
./daily/502.pgsql:# backupdir is relative to ~pgsql home directory unless it begins with a slash:
./daily/502.pgsql:daily_pgsql_backupdir="~${daily_pgsql_user}/backups"
./daily/502.pgsql:daily_pgsql_savedays="7"
./daily/502.pgsql:eval backupdir=${daily_pgsql_backupdir}
./daily/502.pgsql:pgsql_backup() {
./daily/502.pgsql: # daily_pgsql_backupdir must be writeable by user pgsql
./daily/502.pgsql: # ~pgsql is just that under normal circumstances,
./daily/502.pgsql: mkdir -m 700 ${backupdir}; chown ${daily_pgsql_user} ${backupdir}
./daily/502.pgsql: echo "PostgreSQL backups"
./daily/502.pgsql: file=${daily_pgsql_backupdir}/pgglobals_${now}
./daily/502.pgsql: su -l ${daily_pgsql_user} -c \
./daily/502.pgsql: "umask 077; pg_dumpall -g ${daily_pgsql_pgdumpall_globals_args} | gzip -9 > ${file}.gz"
./daily/502.pgsql: su -l ${daily_pgsql_user} -c "umask 077; pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}"
./daily/502.pgsql: -a -mtime +${daily_pgsql_savedays} -delete
./daily/502.pgsql:case "$daily_pgsql_backup_enable" in
./daily/502.pgsql: dbnames=`su -l pgsql -c "umask 077; psql -q -t -A -d template1 -U pgsql -c SELECT\ datname\ FROM\ pg_database\ WHERE\ datname!=\'template0\'"`
./daily/502.pgsql: pgsql_backup $dbnames
./daily/502.pgsql: pgsql_backup $daily_pgsql_backup_enable
./daily/502.pgsql:case "$daily_pgsql_vacuum_enable" in
./daily/502.pgsql: echo "PostgreSQL vacuum"
./daily/502.pgsql: su -l ${daily_pgsql_user} -c "vacuumdb ${daily_pgsql_vacuum_args}"
Seems pretty harmless to me. And in any case, it only runs once daily.
I am pretty sure I have to make a fresh install and import the important databases from dump files. At least, that would show if it is the database configuration or something outside the database server that creates the errors. I am not happy to do it, but I think I have to...
Regards
Jon
pgsql-novice by date: