Re: Several problems with installation on FreeBSD - how to reinstall without loosing data

2013/9/18 Kevin Grittner <kgrittn(at)ymail(dot)com>

> Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com> wrote:
>
> > 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?
>
> ... keeping in mind that many utilities will default user ID and
> 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.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

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