Re: [ADMIN] End of Data test?

[Phillip Smith]

> It seems  like there should be a return code or  
> something that will tell me that there has been a normal completion.

I check for return code 0 in my scripts...
$PG_DUMP --schema=${SCHEMA} ${DBNAME} > ${TMPFILE}
RETVAL=$?
if [ ${RETVAL} -eq 0 ] ; then
echo 'Done!'
else
echo "Error! Return value was ${RETVAL}"
fi

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_restore test procedures (a bit OT)

[Phillip Smith]

> I was just wondering if anyone has any clever way of testing their backups
taken with pg_dump on a daily basis?
I have a daily bash script to backup of one of my databases to file, then
file to tape, which I test restore once per week. I've changed some of my
variables to hard-coded strings to make it shorter and easier to understand
at a quick read. Yes I know it's kinda fschked but it's working atm. I'll
tidy it up eventually


if [ "$DAY" = "Mon" ] ; then
echo "Test restoring database..." >> $LOG_FILE

if [ 1 -eq 1 ] ; then
# Debugging
$ECHO "Restoring from:   ${BACKUP_PATH}"
$ECHO "Restoring to Directory:   ${TMPDNAME}"
$ECHO "Restore Database: ${DBNAME}${DATESTRING}"
$ECHO "Restore Source File:  ${TMPFILE}"
fi

echo -n "Extracting database dump from tape... "
tar xf /dev/st0 --directory /tmp
if [ $? -ne 0 ] ; then
echo "FAIL"
echo "   Failed to Restore from /dev/st0 to /tmp/" >>
$LOG_FILE
SHOW_ERR=true
fi

if [ ${SHOW_ERR} != true ] ; then
### Note: $DATESTRING = yymmdd (eg. 080519)
echo "Creating new database ${DBNAME}${DATESTRING}" >>
$LOG_FILE
/usr/local/bin/psql --command "CREATE DATABASE
${DBNAME}${DATESTRING} ENCODING = 'SQL_ASCII';" > /dev/null
if [ $? -eq 0 ] ; then
echo "Restoring /tmp/psqldump.sql.502 to
${DBNAME}${DATESTRING}" >> $LOG_FILE
/usr/local/bin/psql ${DBNAME}${DATESTRING} <
/tmp/psqldump.sql.502
else
echo "Failed to Create Database!" >> $LOG_FILE
SHOW_ERR=true
fi
fi
Fi


> su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers -c
-d $database $backup_dir/$server-$database-data.gz" >> $logfile
> or 
> su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers -c
-d $database $backup_dir/$server-$database-data.gz >> $logfile"

Perhaps something like this: (it's Monday morning, so I might be suggesting
something stupid)
LOG=`su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers
-c -d $database $backup_dir/$server-$database-data.gz"`
echo $LOG >> $logfile


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] restore postgres system database 8.1.11

[Phillip Smith]

> On a newly created server I stupidly restored over the top of the
> postgres system database using the GUI...(postgresql n00b) and am
> wondering a couple of things. 
>Firstly, if this database is overwritten, is it detrimental to the
running of PostgreSQL? 
>Secondly is it just a simple matter of restoring it using a backup from
a different PostgreSQL instance installed elsewhere. 

When you say "system database", do you mean 'template1'? Or 'template0'? Or
'postgres'? Or something else?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] remove my name from mailing list

[Phillip Smith]

> I want to remove my name from mailing list could you suggest me the
procedure by which i can do this

The headers of every message on the list contains the information you're
after:
List-Archive: 
List-Help: 
List-ID: 
List-Owner: 
List-Post: 
List-Subscribe: 
List-Unsubscribe: 

You need to send a blank message to the List-Unsubscribe address.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Database update problem from crontab on ubuntu server

[Phillip Smith]

> same result when it running trough cronjob. Manually everything is
> fine. Even I put this commands (without su -l ...) in postgres user
> crontab, but same result.
> 
> Server is ubuntu 64bit. Does it makes any difference from 32bit in
> terms of crontab functionality?

System architecture shouldn't affect crontab. Can you give us the
full output from cron? Also, just for debugging's sake, try putting
it in a script and call the script from cron.

If it still fails, then it might help identify exactly where the
error is. If it doesn't fail, then you can start shrinking it all
back down in to one line again and see where the error comes in.

#!/bin/bash

BACKUP_FILE_GZ='/backup/rms.gz'
BACKUP_FILE='/tmp/rms.sql'

echo "---"
echo "Unzipping backup..."
/bin/gunzip -c ${BACKUP_FILE_GZ} > ${BACKUP_FILE}

echo "---"
echo "Attempting restore..."
/usr/bin/psql rms < ${BACKUP_FILE}

echo "---"
echo "Done"


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Database update problem from crontab on ubuntu server

[Phillip Smith]

> su -l postgres -c "gunzip -c /backup/rms.gz | psql rms"

You could also try:
/bin/zcat /backup/rms.gz | /usr/local/postgres/bin/psql rms


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Examining very large dumps

[Phillip Smith]

> vim (linux), vi (FreeBSD) or any other unix editor i tried, could not
handle the volume.

Vi(m) should be able to handle that given enough hardware (CPU and RAM)...
And if hardware is your limitation then no editors like those are going to
be able to handle it.

sed and sed-like tools are going to be the only options.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Slow pg_dump

[Phillip Smith]

(Sorry, hit send too soon!)

> I ran pg_dump with the arguments you suggested, and my 4 GB test
> table finished backing up in about 25 minutes, which seems great.
> The only problem is that the resulting backup file was over 9 GB.
> Using -Z2 resulting in a 55 minute 6GB backup.
> 
> Here's my interpretation of those results: the TOAST tables for
> our image files are compressed by Postgres.  During the backup,
> pg_dump uncompresses them, and if compression is turned on,
> recompresses the backup.  Please correct me if I'm wrong there.
> 
> If we can't find a workable balance using pg_dump, then it looks
> like our next best alternative may be a utility to handle
> filesystem backups, which is a little scary for on-site,
> user-controlled servers.

How about a post-backup compress?
pg_dump -Z0 > uncompressed-backup.sql
gzip uncompressed-backup.sql
mv uncompressed-backup.sql.gz compressed-backup.sql.gz

Your backup is completed in reasonable time, you're just handling
the storage of the backup afterwards, while users can be using the
System again...


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Slow pg_dump

[Phillip Smith]

> I ran pg_dump with the arguments you suggested, and my 4 GB test table
finished
> backing up in about 25 minutes, which seems great.  The only problem is
that the
> resulting backup file was over 9 GB.  Using -Z2 resulting in a 55 minute
6GB backup.
> 
> Here's my interpretation of those results: the TOAST tables for our image
files
> are compressed by Postgres.  During the backup, pg_dump uncompresses them,
and if
> compression is turned on, recompresses the backup.  Please correct me if
I'm wrong
> there.
> 
> If we can't find a workable balance using pg_dump, then it looks like our
next
> best alternative may be a utility to handle filesystem backups, which is a
littlescary for on-site, user-controlled servers.

Ryan

-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED] 
Sent: Saturday, April 12, 2008 9:46 PM
To: Ryan Wells
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Slow pg_dump 

"Ryan Wells" <[EMAIL PROTECTED]> writes:
> We have several tables that are used to store binary data as bytea (in

> this example image files),

Precompressed image formats, no doubt?
 
> pg_dump -i -h localhost -p 5432 -U postgres -F c -v -f
> "backupTest.backup" -t "public"."images" db_name

Try it with -Z0, or even drop the -Fc completely, since it's certainly not
very helpful on a single-table dump.  Re-compressing already compressed data
is not only useless but impressively slow ...

Also, drop the -i, that's nothing but a foot-gun.

regards, tom lane

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Import from CSV

[Phillip Smith]

> When I import a table from csv file by command
> "copy table from './table.csv' with csv header",
> DB Engine add a new line sequence (\n) after a
> new line sequence(\n) in origin data automatically.

Could you do a manual replace afterwards:

UPDATE employee SET address = REPLACE(address, '

', '
');

It's kinda nasty, but it should work...


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Restore Data Folder Problem

[Phillip Smith]

> i have restore my old data folder in pgsql
> but still there is a permission problem

Can you give us any more info? Specific error messages?

In general, permissions should be (I think):
chown -R postgres:postgres /path/to/data
find /path/to/data -type -d -print | xargs chmod 700
find /path/to/data -type -f -print | xargs chmod 600


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] weird network issue

[Phillip Smith]

You're missing this entry from your .11 box:
ACCEPT tcp  --  anywhere anywherestate NEW tcp
dpt:postgres

So iptables goes right to the end and then rejects with "Host Prohibited"
which is reported on your .13 box as "No route to host"

I think the command you want on your .11 box is:
/sbin/iptables --insert RH-Firewall-1-INPUT 19 --protocol tcp
--destination-port 5432 -j ACCEPT


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] bacula problem - failures with no error message in postgres?

[Phillip Smith]

> even on the higher debug levels I am not seeing thing in the postgres logs
Are you increasing the debug levels of Bacula or of Postgres? You'll need to
look in the PG logs and increase their verbosity.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN]

[Phillip Smith]

> I am trying to re-install PostGresSQL on my computer by uninstalling it
first.  

Postgres Version? Windows or Linux?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-admin

Re: [ADMIN] version:8.2.6 pg_hda.conf problem

[Phillip Smith]

> I had these in my pd_hda.conf :
>
> host  all  all  192.168.2.1/254 trust
>
> and  listen_address=*   in postgresql.conf, but it still not work .
>
> ps: I can not find tcpip_  ...in  postgresql.conf.
>
> how can i do now ?

Have you started the Postmaster in 'interactive' mode? You need to pass the
-i option to it.

If you are using pg_ctl:
pg_ctl -D /path/to/data/dir -o "-i" start 


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] trouble with pg_dumpall

[Phillip Smith]

> +
> PGUSER: postgres
> PGDATABASE: canon
> PGHOST: localhost
> PGPASSFILE: /usr/local/pgsql/.pgpass
> PGDATA: /usr/local/pgsql/data
> HOME: /usr/local/pgsql
> PATH:
/usr/local/pgsql/bin:/bin:/usr/kerberos/bin:/usr/local/java/bin:/usr/local/b
in:/bin:/usr/bin:/usr/X11R6/bin:/usr/local/ant/bin:.
> |--|
> *:5432:canon:postgres:mysecret
> |--|
> Password:
> pg_dumpall: could not connect to database "template1": fe_sendauth: no
password supplied

As Jeff said, that's your problem... Change 'canon' to '*' and it should be
fine :)


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] trouble with pg_dumpall

[Phillip Smith]

> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tena Sakai
> Sent: Thursday, 28 February 2008 15:05
> To: Steve Holdoway; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] trouble with pg_dumpall
> 
> As you can see, I am afraid my point still holds.

Try adding this to your script after the echo PATH: $PATH line, just for
debugging purposes:

/bin/echo "|--|"
if [ -f ~/.pgpass ] ; then
/bin/cat ~/.pgpass
else
/bin/echo ".pgpass missing"
fi
/bin/echo "|--|"


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] trouble with pg_dumpall

[Phillip Smith]

Can you post your pg_hba.conf file to the list please?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> > I wonder what it's meaning by invalid arg?
> 
> On my Fedora machine, "man write" explains EINVAL thusly:
> 
>EINVAL fd  is attached to an object which is unsuitable for
writing; or
>   the file was opened with  the  O_DIRECT  flag,  and  either
the
>   address  specified  in buf, the value specified in count, or
the
>   current file offset is not suitably aligned.
> 
> I'm not sure that writing to a tape is quite like O_DIRECT, but the
mention of
> an un-aligned count seems pretty relevant.  If you grant the assumption
that
> the underlying problem is that the tape drive has to be written to in
> multiples of its blocksize, then this supports the idea that a violation
of
> that rule would be reported as EINVAL.

Interesting... If I set the block-size to 32, everything goes without any
error,
But not ending with "PostgreSQL database dump complete":

[EMAIL PROTECTED] ~]$ mt setblk 32
[EMAIL PROTECTED] ~]$ mt stat
SCSI 2 tape drive:
File number=0, block number=0, partition=0.
Tape block size 32 bytes. Density code 0x25 (DDS-3).
Soft error count since last status=0
General status bits on (4101):
 BOT ONLINE IM_REP_EN
[EMAIL PROTECTED] ~]$ pg_dump dbname > /dev/st0
[EMAIL PROTECTED] ~]$ tail - < /dev/st0
REVOKE ALL ON TABLE stock_test FROM dbuser;
GRANT ALL ON TABLE stock_test TO dbuser;
GRANT ALL ON TABLE stock_test TO PUBLIC;


--
-- Name: stores; Type: ACL; Schema: public; Owner: dbuser
--

[EMAIL PROTECTED] ~]$


If I set the block-size to 0... Well it's really not happy:

[EMAIL PROTECTED] ~]$ mt setblk 0
[EMAIL PROTECTED] ~]$ mt stat
SCSI 2 tape drive:
File number=0, block number=0, partition=0.
Tape block size 0 bytes. Density code 0x25 (DDS-3).
Soft error count since last status=0
General status bits on (4101):
 BOT ONLINE IM_REP_EN
[EMAIL PROTECTED] ~]$ pg_dump dbname > /dev/st0
[EMAIL PROTECTED] ~]$ tail - < /dev/st0
tail: error reading `standard input': Cannot allocate memory
[EMAIL PROTECTED] ~]$ cat /dev/st0 > /tmp/test-restore.sql
cat: /dev/st0: Cannot allocate memory
[EMAIL PROTECTED] ~]$


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] trouble with pg_dumpall

[Phillip Smith]

Try explicitly setting those variables in your script:
$PGUSER
$PGDATABASE
$PGHOST
$PGPASSFILE
$PGDATA


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

> > > >  Do we think this is a Postgres problem, a Linux problem or a
> > > >  problem specific to my hardware setup? Was I wrong to think
> > > >  that I should be able to  stream directly from pg_dump to
> > > >  /dev/st0? I would have thought it *should*  work, but maybe
> > > >  I was wrong in the first place with that?

> >  > If you can dd the file onto your tape drive, then it's some
> >  > weird interaction between pg_dump and your system I'd think.
> >  > Could the be some maximum size that you can buffer through
> >  > pipes / redirects on your machine?
> >  >
> >  > I'd test to see if cat pgdumpfile.sql > /dev/st0 works or not.
> >  > If it fails at the same approximate size, then it's something
> >  > to do with redirection.  If tar works but redirects fail, then
> >  > the problem isn't with postgresql.  i.e. do something similar
> >  > to what you're doing with pgsql and see which things fail and
> >  > which ones don't.

> >  It appears to be me :(
> >
> >  [EMAIL PROTECTED] ~]$ cat /tmp/dbname080225.sql > /dev/st0
> >  cat: write error: Invalid argument
> >
> >  It ran for a good 30 minutes, then died with that.

> I wonder what it's meaning by invalid arg?  Is something in the
> .sql file somehow coming across as an argument?  Can you cat the
> sql file to /dev/null successfully?

Yes.
[EMAIL PROTECTED] ~]$ cat /tmp/dbname080225.sql > /dev/null
[EMAIL PROTECTED] ~]$


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

> >  Do we think this is a Postgres problem, a Linux problem or a problem  
> > specific to my hardware setup? Was I wrong to think that I should be 
> > able to  stream directly from pg_dump to /dev/st0? I would have 
> > thought it *should*  work, but maybe I was wrong in the first place 
> > with that?
> 
> If you can dd the file onto your tape drive, then it's some weird
interaction between
> pg_dump and your system I'd think.  Could the be some maximum size that
you can buffer
> through pipes / redirects on your machine?
> 
> I'd test to see if cat pgdumpfile.sql > /dev/st0 works or not.  If it
fails at the
> same approximate size, then it's something to do with redirection.  If >
tar works but
> redirects fail, then the problem isn't with postgresql.  i.e. do something
similar to
> what you're doing with pgsql and see which things fail and which ones
don't.

It appears to be me :(

[EMAIL PROTECTED] ~]$ cat /tmp/dbname080225.sql > /dev/st0
cat: write error: Invalid argument

It ran for a good 30 minutes, then died with that.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

Sorry Steve, I missed the "reply all" by 3 pixels :)

> > > tar -cf -
> > > 
> > > the '-f -' says take input.
> > 
> > That would be to write to stdout :) I can't figure out how to accept
> > from stdin :(
> > 
> > -f is where the send the output, either a file, a device (such as
> > tape) or stdout (aka '-')
> 
> Not quite. tar cf - will pipe to stdout, but tar xf - will pipe from 
> stdin.

Yes, true; my head was in "tar c" mode :)

> For the OP's problem, I'd try piping through dd as a file buffer, and 
> run sync on completion. Is there a /dev/rst0, like on some of the old 
> unixes (doesn't look like it after checking my CentOS 4 server)? The 
> difference was that rst0 was unbuffered, and st0 was buffered. Either 
> way, the sync may help.

There's no /dev/rst0 block device, but I'm more familiar with tar than dd,
so I think I'll just rewrite the script to tar to a temp file first :)

Do we think this is a Postgres problem, a Linux problem or a problem
specific to my hardware setup? Was I wrong to think that I should be able to
stream directly from pg_dump to /dev/st0? I would have thought it *should*
work, but maybe I was wrong in the first place with that?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

>> What would the correct syntax be for that - I can't figure out how to 
>> make tar accept stdin:

> I don't think it can.  Instead, maybe dd with blocksize set equal to the
tape drive's required blocksize would do?  You'd have to check what options
your
> dd version has for padding out the last partial block. Padding with spaces
should work fine, not totally sure if nulls would be OK.

I don't think it can either, which kind of makes sense. Tar is an archiving
utility to create an archive of files; not to create an archive of the
contents of files. Subtle difference, but makes sense. If I created a tar
archive from the stream coming out of tar, what would be listed when I did
'tar tvf /dev/st0'?

I think I'll hack the backup to output it to a temp file, then tar that file
to tape.

At the very least:

#!/bin/bash

$DBNAME='dbname'
$TMPFILE="/tmp/$DBNAME.date.sql"
$TAPE_DRIVE='/dev/st0'

/usr/local/bin/pg_dump $DBNAME > $TMPFILE
/bin/tar cvf $TAPE_DRIVE --label="$TMPFILE" $TMPFILE
/bin/rm -f $TMPFILE


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

> Coming in the middle of this thread, so slap me if I'm off base here. 
> tar will accept standard in as:
> 
> tar -cf -
> 
> the '-f -' says take input.

That would be to write to stdout :) I can't figure out how to accept from
stdin :(

-f is where the send the output, either a file, a device (such as tape) or
stdout (aka '-')


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] Backup to Tape Incomplete

[Phillip Smith]

On Sun, Feb 24, 2008 at 9:20 PM, Phillip Smith
<[EMAIL PROTECTED]> wrote:
>> PostgreSQL 8.2.4
>>  RedHat ES4
>>
>>  I have a nightly cron job that is (supposed) to dump a specific 
>> database to  magnetic tape:
>> /usr/local/bin/pg_dump dbname > /dev/st0
>>
>>  This runs, and doesn't throw any errors, but when I try to restore it 
>> fails  because the tape is incomplete:

> A couple of possible things to try; pg_dump to a text file and try
cat'ting that to the tape drive, or pipe it through tar and then to the
tape.

What would the correct syntax be for that - I can't figure out how to make
tar accept stdin:

[EMAIL PROTECTED] ~]$ pg_dump dbname | tar cvf /dev/st0
tar: Cowardly refusing to create an empty archive
Try `tar --help' for more information.
[EMAIL PROTECTED] ~]$ pg_dump dbname | tar cvf /dev/st0 -
tar: -: Cannot stat: No such file or directory
[EMAIL PROTECTED] ~]$ 


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[ADMIN] Backup to Tape Incomplete

[Phillip Smith]

PostgreSQL 8.2.4
RedHat ES4

I have a nightly cron job that is (supposed) to dump a specific database to
magnetic tape:
/usr/local/bin/pg_dump dbname > /dev/st0

This runs, and doesn't throw any errors, but when I try to restore it fails
because the tape is incomplete:
[EMAIL PROTECTED] ~]$ cat /dev/st0 | tail


--
-- Name: rewards_points; Type: ACL; Schema: public; Owner: dbname
--

REVOKE ALL ON TABLE rewards_points FROM PUBLIC;
REVOKE ALL ON TABLE rewards_points FROM dbname;
GRANT ALL ON TABLE rewards_points TO dbname;
GRANT SELECT,INSERT,UPDATE ON TABLE rewards_points TO
[EMAIL PROTECTED] ~]$

As you can see, the "end of file" is half-way through a SQL statement, and
doesn't even have a new-line marker.

The database is not too big for the tape - it's a DDS-3 tape drive (12/24gb)
and the database is not even 1gb:
[EMAIL PROTECTED] ~]$ pg_dump dbname > /tmp/dbname080225.sql
[EMAIL PROTECTED] ~]$ ls -lh /tmp/dbname080225.sql
-rw-r--r--  1 postgres root 957M Feb 25 13:42 /tmp/dbname080225.sql

Is this a PostgreSQL issue or a tape drive issue? I can use tar to read and
write without any problems.

Do I need to change the block size on the tape drive?

[EMAIL PROTECTED] ~]$ mt stat
SCSI 2 tape drive:
File number=0, block number=0, partition=0.
Tape block size 512 bytes. Density code 0x25 (DDS-3).
Soft error count since last status=0
General status bits on (4101):
 BOT ONLINE IM_REP_EN


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Log details are displaying on the console

[Phillip Smith]

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Suresh Gupta VG
Sent: Tuesday, 19 February 2008 21:17
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Log details are displaying on the console
 
> All the logging details of pgsql are displaying on the console itself. Pls
see the
> below output. Is there any option in the configuration files to disable
it.?
 
Have a look in postgresql.conf in your data directory under the heading:

#---
# ERROR REPORTING AND LOGGING
#---


Specifically these settings:
log_destination
redirect_stderr


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] Postgres Backup and Restore

[Phillip Smith]

> "Phillip Smith" <[EMAIL PROTECTED]> writes:
> > I've been thinking about this, and while I don't agree it's a bug, I
> think
> > that perhaps PostgreSQL should raise a notice or warning that the
> > destination database has different encoding than the file being
> restored...?
> 
> If Postgres actually *knows* that the encodings are different, it can
> deal with that.  The cases that are problematic are where the software
> has been misinformed for one reason or another.  I doubt that a warning
> issued (or not) on the basis of misinformation will be especially helpful.

How has the software been misinformed and how can we avoid it then? :)

The start of the pgdump sets the client encoding, so shouldn't that tell PG
that things could get strange?


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] Postgres Backup and Restore

[Phillip Smith]

> > Are you sure that the destination database has the same encoding
> > declaration as the source did?
> 
> That's what I'd be looking at - I had similar problems a few weeks ago.
> See
> this thread:
> http://archives.postgresql.org/pgsql-admin/2007-12/msg00235.php

I've been thinking about this, and while I don't agree it's a bug, I think
that perhaps PostgreSQL should raise a notice or warning that the
destination database has different encoding than the file being restored...?

Obviously I'm not the only one (seemingly) caught out by this so I'm sure it
would save some heartache for people.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] Postgres Backup and Restore

[Phillip Smith]

> Are you sure that the destination database has the same encoding
> declaration as the source did?

That's what I'd be looking at - I had similar problems a few weeks ago. See
this thread:
http://archives.postgresql.org/pgsql-admin/2007-12/msg00235.php



THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] Backup

[Phillip Smith]

> We have a center in Europe who has just started to use PostgreSQL and was
> asking me if there are any Symantec product or other products that backup
> this type of database.

It doesn't appear to. I've just been through the whole rigmarole of
BackupExec for some Windows Servers, and I couldn't find anything to deal
with PostgreSQL.

Just dump Postgres to the File System and backup that dump. Depends on what
your Recovery Point requirements are.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] pg_restore errors

[Phillip Smith]

> Trying to restore a pg_dump I get this:
> 
> pg_restore: ERROR:  invalid byte sequence for encoding "UTF8": 0xae
> HINT:  This error can also happen if the byte sequence does not match the
> encoding expected by the server, which is controlled by "client_encoding".
> CONTEXT:  COPY imm_lookup, line 2
> pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  invalid
> byte
> sequence for encoding "UTF8": 0xae
> HINT:  This error can also happen if the byte sequence does not match the
> encoding expected by the server, which is controlled by "client_encoding".
> CONTEXT:  COPY imm_lookup, line 2

I had a similar issue a week or 2 ago - turns out my original database was
using SQL_ASCII encoding, not UTF-8.

Can your client tell you what encoding they're using? Otherwise, just try a
few different ones. Someone else might have a tricky way to work out what
the encoding should be based on the dump (but I don't ;))

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [ADMIN] Restoring pg_dump Backup with psql Fails

[Phillip Smith]

> Yeah, but what other user-created stuff (functions for instance) might
> be in there?
> You might want to drop template1 and recreate it from template0.
> (There's a blow-by-blow article about doing that on techdocs, IIRC.)

I think I'll do that :)
Thanks Tom & Joshua - you've been most helpful as always,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] Restoring pg_dump Backup with psql Fails

[Phillip Smith]

> You have several problems in this dump. One it appears that some of the
> releations already exist. Did you by chance accidently create stuff
> within template1 at some point?

I think I've found my problem:
[EMAIL PROTECTED] backups]$ psql template1
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
template1=# \d
List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | buyers   | table | postgres
 public | creditors| table | postgres
 public | debtors  | table | postgres
 public | grps | table | postgres
 public | po   | table | postgres
 public | sales_orders | table | postgres
 public | stkmvt   | table | postgres
 public | stock| table | postgres
 public | sys  | table | postgres
(9 rows)

template1=#

I don't know how or when those tables were created in template1 - they're
certainly not supposed to be there or needed. Is it safe to just DROP the
tables from template1?

> Secondly you need to look into iconv to fix your invalid UTF8 data.
My bad - the old database was using SQL_ASCII encoding, but when I recreated
it I let it default to UTF8.

Re-created it again with SQL_ASCII encoding, which didn't copy the tables
from template1 and now the restore seems to be going OK :)


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] separating PG binaries into a ro mounted partition

[Phillip Smith]

What are you trying to acheive by making the binaries read-only?

All the config files reside in the data directory so there shoudl be a
problem making /usr/local/postgres/bin (or whatever you choose to
install the binaries) to be a read-only mounted partition. The other
option (which is far easier) would be to set the binaries immutable. As
root, run this:
chattr +i /use/local/postgres/bin/*

That way only root can remove the immutable flag so they can be changed
- if it's a security issue behind your desire to do this then it doesn't
matter that root can do that versus your suggestion as root would be
able to just remount the partition rw:
mount -o remount,rw /usr/local/postgres

If you can advise why you want them to be read-only then we can make
better suggestions.

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Re: [ADMIN] Restoring pg_dump Backup with psql Fails

[Phillip Smith]

Sorry Tom - I should know better :)

I've attached the first 512 lines of the output from psql. The whole output
is 3mb.

Cheers,
~p

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Saturday, 29 December 2007 13:26
> To: Phillip Smith
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Restoring pg_dump Backup with psql Fails
> 
> "Phillip Smith" <[EMAIL PROTECTED]> writes:
> > But I get a whole bunch of errors, for example:
> > 
> > ERROR:  syntax error at or near "all"
> > LINE 1: all orders over $400.00 - freight free Order 10 of any produ...
> > ^
> 
> Please show us the *first* few errors, not some randomly chosen ones.
> The above looks like a COPY has failed and psql has started thinking
> that copy-data lines are commands, but how or why that happened is
> not in evidence.
> 
>   regards, tom lane


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.
SET
SET
SET
SET
SET
COMMENT
CREATE LANGUAGE
SET
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
 setval 

  1
(1 row)

SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE VIEW
ALTER TABLE
CREATE VIEW
ALTER TABLE
ERROR:  relation "grps" already exists
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
COMMENT
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
COMMENT
ERROR:  relation "stock" already exists
ALTER TABLE
COMMENT
COMMENT
ERROR:  column "wholesale" of relation "stock" does not exist
ERROR:  column "discount" of relation "stock" does not exist
ERROR:  column "hl_wholesale" of relation "stock" does not exist
ERROR:  column "tax_rate" of relation "stock" does not exist
CREATE TABLE
ALTER TABLE
COMMENT
ERROR:  column s.supplier_code does not exist
LINE 2: ... g.category, s.grp, s.brand, s.style, s.supplier, s.supplier...
 ^
ERROR:  relation "public.stock_full_detail" does not exist
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
ERROR:  invalid byte sequence for encoding "UTF8": 0x96
HINT:  This error can also happen if the byte sequence does not match the 
encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY dwh_cust, line 7413
ERROR:  invalid byte sequence for encoding "UTF8": 0xe86d65
HINT:  This error can also happen if the byte sequence does not match the 
encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY dwh_stk, line 323461
ERROR:  column "use_barcodes" of relation "grps" does not exist
invalid command \
invalid command \
Query buffer reset 

[ADMIN] Restoring pg_dump Backup with psql Fails

[Phillip Smith]

PostgreSQL 8.2.4 on RedHat ES4 64-bit

Hi all,

Doing some works on a database this morning (changing it to take data from
our new ERP system) so naturally I made a backup of the database first:
  pg_dump important_database > ~/backup/important-071229.sql
chmod 440 ~/backup/important-071229.sql

Of course things have screwed up (fortunately no-one is working today) and I
need to restore:
  psql
ALTER DATABASE important_database RENAME TO
important_database071229;
CREATE DATABASE important_database;
\q

psql important_database < ~/backup/important-071229.sql

But I get a whole bunch of errors, for example:

ERROR:  syntax error at or near "all"
LINE 1: all orders over $400.00 - freight free Order 10 of any produ...
^
ERROR:  syntax error at or near "If"
LINE 1: If stores spend over $300.00 they will receive a 5% discount...

invalid command \.
invalid command \.
invalid command \N
invalid command \N


Both psql and pg_dump are the correct versions:
[EMAIL PROTECTED] ~]$ ls -lh `which pg_dump`
lrwxrwxrwx  1 root root 31 Jul 25 11:51 /usr/local/bin/pg_dump ->
/usr/local/pg-8.2.4/bin/pg_dump
[EMAIL PROTECTED] ~]$ ls -lh `which psql`
lrwxrwxrwx  1 root root 28 Jul 25 11:51 /usr/local/bin/psql ->
/usr/local/pg-8.2.4/bin/psql
(I compile each new version of Postgres to it's own folder and data dir:
/usr/local/pg-/ and /home/Postgres/pgData- )

What am I doing wrong?

Cheers,
~p

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Dump database more than 1 flat file

[Phillip Smith]

> >> In other words, by WRONGLY attaching confidentiality notices, courts
> >> might decide to ignore them even in cases where you imagined they
> >> ought to be legitimate...
> 
> > I've thought about that before and agree completely. Unfortunately this
> > disclaimer is added by our perimeter UTM device, which has no way to
> know
> > what is 'public' and what is 'confidential'
> 
> Which, indeed, is exactly the sort of fact that would invalidate any
> subsequent claims of confidentiality based on the notice.  You should
> point out to your organization's lawyers that this type of requirement
> accomplishes nothing except making them look like fools.
> 

I've forwarded it to the powers that be and they can decide what they want
to do. I'm just the IT Manager to make the magic happen when they decide
they want it ;D


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] Dump database more than 1 flat file

[Phillip Smith]

> You should think before you attach these sorts of "confidentiality"
> notices.
> 
> If you put them on messages like this that are *OBVIOUSLY* being sent
> to publicly-available mailing lists, this may undermine future claims
> of material being supposedly-confidential.
> 
> In other words, by WRONGLY attaching confidentiality notices, courts
> might decide to ignore them even in cases where you imagined they
> ought to be legitimate...

I've thought about that before and agree completely. Unfortunately this
disclaimer is added by our perimeter UTM device, which has no way to know
what is 'public' and what is 'confidential'


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] including variables in copy command

[Phillip Smith]

Could you wrap it in a transaction block?

begin;

select *
into temp_table
from employee
where hire_date between begin_date and end_date;

copy temp_table to '/var/data/text.txt' delimiter ';';

commit;


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] Dump database more than 1 flat file

[Phillip Smith]

Try splitting by line count instead of size:
  -l, --lines=NUMBER
put NUMBER lines per output file

pg_dump | split -l 32000

You'll have to experiment to find how many lines will give you a roughly
appropriate size for your purposes.

Alternatively, another option is to get an USB external HDD (I picked up a
Seagate 120gb 2.5" one recently for AU$135) and dump your data to that, then
take that to the new server. Any recent 2.6 kernel shouldn't have any issues
mounting it (especially formatted as ext2/3 or jfs/xfs etc)

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [ADMIN] Dump database more than 1 flat file

[Phillip Smith]

> If doesn't have another way, how can I put the
> "header" in the begin of file without open?
> With "cat >>" command I put in the end.

After the split files are loaded on the new server file system:
cat splitfile*.txt > bigfile.txt
psql < bigfile.txt


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

[ADMIN] Visual Basic ADODB Connection Problem

[Phillip Smith]

Hi All,

I posted this on the ODBC list a couple of days ago but haven't had any
reply - not sure if I had the right list. Perhaps it's an admin thing since
it works on one server but not another, maybe it's something obscure between
how I've setup the server that isn't working...?

Cheers,
~p

(Forwarded message below)

> I'm hoping I'm posting this to the right place.
> 
> I've been using PostgreSQL for a couple of years now. I'm successfully
> running several PG servers at work for Data Warehousing and one at home
> for various things (drupal, amarok, development)
> 
> I've decided to experiment with Visual Basic and PostgreSQL as there's
> something I want to develop where I think VB would be best (mainly because
> the only other languages I know are PHP and PICK)
> 
> My problem is, I use the connection string below to connect to the server
> at work, and it connects fine; I can execute SELECT queries etc:
>   strPG = "Driver={PostgreSQL};" & _
> "Server=sql.weatherbeeta.com.au;" & _
> "PORT=5432;" & _
> "Database=dwh;" & _
> "UID=postgres;" & _
> "PWD=thisisnotreallymypassword"
> 
> But when I take the same string and modify it to connect to my home server
> (via VPN) it fails:
>   strPG = "Driver={PostgreSQL};" & _
> "Server=192.168.1.234;" & _
> "PORT=5432;" & _
> "Database=develop;" & _
> "UID=postgres;" & _
> "PWD=anotherpassword"
> 
> The error message in VB is:
>   "The database does not exist on the server or user authentication
> failed."
> 
> The logs on my home server say:
> 2007-11-12 17:38:22 ESTLOG:  connection received: host=172.20.0.155
> port=1148
> 2007-11-12 17:38:22 ESTLOG:  connection authorized: user=postgres
> database=develop
> 2007-11-12 17:38:22 ESTLOG:  duration: 0.173 ms
> 2007-11-12 17:38:22 ESTLOG:  disconnection: session time: 0:00:00.142
> user=postgres database=develop host=172.20.0.155 port=1148
> 
> I can connect using the same credentials via psql running on one of my
> servers at work fine (172.20.0.155 is my work IP address), so it doesn't
> appear to be incorrect username / password or firewall etc.
> 
> My only thought is that I'm using the wrong auth type in pg_hba, but
> they're both the same at work and at home (md5)
> 
> Anyone help me or point me in the right direction?
> 
> Both servers are v8.2 under Linux; work one is 64-bit and home is 32-bit.
> 
> Cheers,
> ~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] Upgrading from 7.2.1 to 8.x

[Phillip Smith]

You can have a look through all the release notes from 7.2.1 to current to
see what will be fixed, including any data eating insects. Check this page
http://www.postgresql.org/docs/7.4/static/release.html

 

Obviously you'll need to test everything, but you should be able to install
the new version, use pg_dumpall to dump your DB, and then restore it to the
new version. Any errors that come up will need to be investigated.

 

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Chris Hoover
Sent: Thursday, 1 November 2007 04:28
To: pgsql-admin@postgresql.org Admin
Subject: [ADMIN] Upgrading from 7.2.1 to 8.x

 

Question,

I have an ancient database that is running Postgresql 7.2.1 on AIX.  Is it
safe to directly upgrade the database to 8.1?  If not, what steps do I need
to take? 

I have been pushing for 3 years to upgrade this database, and with some
upcoming projects, I am going to do another push with management to upgrade
this database. 

Also, I know there are a ton of fixes/improvements from 7.2, but are there
any major data losing bugs in this version that I can use as added pressure
on management?

Thanks,

Chris



THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Re: [ADMIN] High system CPU usage during query run

[Phillip Smith]

Is this a Windows or Linux based system? What version of PostgreSQL? What
else is running on the server?

 

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Abu Mushayeed
Sent: Tuesday, 30 October 2007 14:49
To: pgsql-admin@postgresql.org
Subject: [ADMIN] High system CPU usage during query run

 

Hello,

 

I have a problem with my database that I cannot fugure out. We have queries
running on the server and we observe that the sytem CPU% usage goes to 97 -
98% and the quries take tremendous amount of time to finish. On other days
the queries would run quick and the system CPU usage percentage is low.

 

We have done disk scans and did not find issue as per the sys admin. What
from the database perspective I should investigate to resolve the issue?

 

Our server has dual core Intel Xeon processors 2.66 GHZ, 16GB RAM and RAID10
1.5 TB.

 

Please I need advise on this problem.

 

Thanks

Abu

 __
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 



THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Re: [ADMIN] 32-bit to 64-bit migration screwup

[Phillip Smith]

I'm assuming you've made a jump in database version - ie, 7.4 to 8.2 or
similar.

 

You need to use initdb to create new database files, then pg_dumpall on the
old system to dump the data out of the old before importing to the new. Make
sure you use pg_dumpall from the new version to dump the data from the old
database.

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of David Young
Sent: Friday, 26 October 2007 15:52
To: pgsql-admin@postgresql.org
Subject: [ADMIN] 32-bit to 64-bit migration screwup

 

All,

 

I was hoping someone can help me fix a big blunder.  I recently bought a new
system to replace the old one.  The new system is running 64bit linux and
the old one 32bit.  I installed PG 64-bit and tried to bring the old
database back up.  Even though I'm running the same version of PG, it is
giving me an error when I try to start it back up.  below is the message
from pgstartup.log.  It looks like it doesn't recognize the control file
anymore.

 

Am I screwed?  I don't have the old system anymore and I didn't do a dump.
I figure if I'm running the same version of PG, the data will come back up
correctly.  If someone can help me out of this jam, I would greatly
appreciate it. 

 

Thanks,

David

 

--pgstartup.log---

The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 24MB/153600
creating configuration files ... ok 
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok 

Success. You can now start the database server using:

/usr/bin/postgres -D /var/lib/pgsql/data
or
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

LOG:  logger shutting down
LOG:  logger shutting down
LOG:  logger shutting down
LOG:  logger shutting down
LOG:  logger shutting down
FATAL:  incorrect checksum in control file
FATAL:  incorrect checksum in control file 
FATAL:  incorrect checksum in control file



THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Re: [ADMIN] l need help

[Phillip Smith]

> could somebody help to install drivers for postgresql on redhat 9
See http://www.postgresql.org/docs/8.2/static/installation.html

And specifically, from http://www.postgresql.org/docs/8.2/static/install-
procedure.html

Client-only installation: If you want to install only the client
applications and interface libraries, then you can use these commands:
gmake -C src/bin install
gmake -C src/include install
gmake -C src/interfaces install
gmake -C doc install


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] Please help (backup)

[Phillip Smith]

(Please reply to the list so everyone can keep track of the thread)

 

You’ll need to look at the table definitions to work out what the data should 
look like, and if it doesn’t look like that – then it’s probably part of the 
corrupt data…

 

 

-Original Message-
From: Anibal David Acosta [mailto:[EMAIL PROTECTED] 
Sent: Monday, 22 October 2007 15:33
To: Phillip Smith
Subject: Re: [ADMIN] Please help (backup)

 

I open with word pad & notepad, but table, functions and other object 
declarations looks like "plain text" but the DATA nope.

This is the beginnig of something that looks as DB object
-
ALTER TABLE ONLY threads
ADD CONSTRAINT threads_fk1 FOREIGN KEY (forum_id) REFERENCES forums(id) ON 
DELETE RESTRICT;
 =   ALTER TABLE ONLY public.threads DROP CONSTRAINT threads_fk1;
   public   postgresfalse 

  _  

   1488 
  _  

   1869 
  _  

   1464  
-


This is the beginnig of something that looks as DB object
-
 xœeYÝzÚ¸½Þ}
Ï͹˜ïk¶1†»!¤¤$á MÛ3gÊ'Œ Ùbd;=É74Wç]æQæ
Î’Im·?!^–-ikk­µE—†CæjgJš½‰ÜÕ…È•VÂÒÅ›Ð]_ÊBZ
-

BTW, I used the "PGAdmin" application to make the backup, I don't have any BLOB 
column, just normal data type (integer, varchar, datetime)


As you can see, the "data" section is not human readable, so I cant  modify it.


Thanks for your time Phillip, I hope  to find a solution.

regards.

Anibal

Phillip Smith escribió: 

The dumped file is just a plain-text file (Depending on the data types
within your database)
 
You should be able to open the backup you made with pg_dump in your
favourite editor such as vi and search through for the dodgy data. Clear out
the bad stuff, write it back to disk and you should be good to go.
 
Obviously this won't "save" the damaged portion of the data, and it may have
unpredictable effects when you take in to account any foreign keys etc you
may have in the database that depends on this data.
 
Cheers,
~p
 
  

-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-admin-
[EMAIL PROTECTED] On Behalf Of Anibal David Acosta
Sent: Monday, 22 October 2007 11:16
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Please help (backup)
 
I have a backup done with "pg_dump" utility.
 
For some reason the backup file has 8 phisical bad sectors (17 KB of data)
 
Now when I try to restore the backup all tables are created successfully
and data of almost all tables restores successfully too, but one table
(the most important) appear empty.
 
When I do a pg_restore  its show all sql instructions and
data until arrive to the "damaged sector" and throws a "out of memory"
exception.
 
I want to know if is possible to fix the backup file removing the blocks
in the "bad sectors" in order to restore all other information.
 
something like file dump surgery :)
 
Thanks!
 
Anibal
 
 
---(end of broadcast)---
TIP 4: Have you searched our list archives?
 
   http://archives.postgresql.org


 
 
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
 
***Confidentiality and Privilege Notice***
 
The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
 
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.
 
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly
 
 
  


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not 

Re: [ADMIN] Please help (backup)

[Phillip Smith]

The dumped file is just a plain-text file (Depending on the data types
within your database)

You should be able to open the backup you made with pg_dump in your
favourite editor such as vi and search through for the dodgy data. Clear out
the bad stuff, write it back to disk and you should be good to go.

Obviously this won't "save" the damaged portion of the data, and it may have
unpredictable effects when you take in to account any foreign keys etc you
may have in the database that depends on this data.

Cheers,
~p

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-admin-
> [EMAIL PROTECTED] On Behalf Of Anibal David Acosta
> Sent: Monday, 22 October 2007 11:16
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Please help (backup)
> 
> I have a backup done with "pg_dump" utility.
> 
> For some reason the backup file has 8 phisical bad sectors (17 KB of data)
> 
> Now when I try to restore the backup all tables are created successfully
> and data of almost all tables restores successfully too, but one table
> (the most important) appear empty.
> 
> When I do a pg_restore  its show all sql instructions and
> data until arrive to the "damaged sector" and throws a "out of memory"
> exception.
> 
> I want to know if is possible to fix the backup file removing the blocks
> in the "bad sectors" in order to restore all other information.
> 
> something like file dump surgery :)
> 
> Thanks!
> 
> Anibal
> 
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] pg_catalog.pg_roles DESTROYED?

[Phillip Smith]

Sounds like you're using a newer version of psql to connect to the older
host.

psql 8.2.4 would throw up errors like that when connecting to a 7.4.7 box -
when you login to the old box, then run psql, you would be running the old
version of psql and hence the error disappears.

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ralph Smith
Sent: Friday, 3 August 2007 10:07
To: pgsql-admin@postgresql.org
Subject: [ADMIN] pg_catalog.pg_roles DESTROYED?

I'm preparing to upgrade a 7.4.7 DB to 8.2.4.
I just got connectivity from new to old (different hosts), and being  
a newhire and no-one had ever done any maintenance on the old DB, I  
connected as joe-user to the old DB using the client of the new.
(joe-user is the owner of the DB)

I  did some analyze and vacuum-ing on two tables.
The last thing I did there was
 >vacuum full verbose tablename;
I did it twice to see the differing results.
It was HUGE and now is smaller.

I next tried to do a \dt and suddenly

airburst=> \dt
ERROR:  relation "pg_catalog.pg_roles" does not exist

None of the \d - commands work.

Only if I go in as PG super can I avoid that error.  (Going in  
locally after logging into that box.)

I'm new to PG myself, so I'd appreciate it if I had some hand-holding.

Thank you!
Ralph Smith

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] Data directory was initialized by PostgreSQL version 8.3

[Phillip Smith]

"Phillip Smith" <[EMAIL PROTECTED]> writes:
>> Now PostgreSQL complains at me with the following error:
>> 2007-07-25 11:52:05 EST @ []DETAIL:  The data directory was initialized
by
>> PostgreSQL version 8.3, which is not compatible with this version 8.2.4.

>> As far as I'm aware, 8.3 doesn't exist!

> You seem to have pulled a development snapshot from someplace ...

Thanks Tom... I recall you giving me a patch for an error I was having when
I first upgraded to 8.2 but I can't for the life of me remember the error.

Is it worth me trying to fix this stuff up on my behalf? Or should I just go
back to last night's backup and recreate this morning's transactions?


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] Data directory was initialized by PostgreSQL version 8.3

[Phillip Smith]

> I'm assuming the problem is because the PG_VERSION file in the data
> directory says 8.3, so I'm making a copy of the directory, changing that
to
> 8.2 and see if it will start with that.

Well that didn't work!
[EMAIL PROTECTED] pgData-8.2]$ pg_ctl -o '-i' start
server starting
2007-07-25 12:13:25 EST @ []FATAL:  database files are incompatible with
server
2007-07-25 12:13:25 EST @ []DETAIL:  The database cluster was initialized
with PG_CONTROL_VERSION 831, but the server was compiled with
PG_CONTROL_VERSION 822.
2007-07-25 12:13:25 EST @ []HINT:  It looks like you need to initdb.


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[ADMIN] Data directory was initialized by PostgreSQL version 8.3

[Phillip Smith]

Hi All,

Just tried to recompile (what I thought!) was the same version of PG that I
already had running, to add in the AdminPack to stop pgAdmin complaining
about it being missing.

After recompiling from source, with the Admin Pack (did it on a devel box
first to make sure I was doing the right thing), I did the following:
1. Shutdown PostgreSQL
2. 'gmake install' the recompiled version (same -prefix = /usr/local/pg-8.2)
3. Restart PostgreSQL

Now PostgreSQL complains at me with the following error:
2007-07-25 11:52:05 EST @ []DETAIL:  The data directory was initialized by
PostgreSQL version 8.3, which is not compatible with this version 8.2.4.

As far as I'm aware, 8.3 doesn't exist! The latest version on the
postgresql.org website is 8.2.4

I'm assuming the problem is because the PG_VERSION file in the data
directory says 8.3, so I'm making a copy of the directory, changing that to
8.2 and see if it will start with that.

I can initialize a new data directory and restore last nights backup, but
I'd rather not have to loose everything that's happened in the last 4 hours.

Any advice on what's going on?

Cheers,
~p


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this
e-mail.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] initdb problem

[Phillip Smith]

So did initdb execute without errors after you changed the home directory?

It looks like your postgresql.conf file is pointing Postgres to log to a
directory / file that the Postgres user doesn't have permission to write to.

~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joe Barwell
Sent: Thursday, 21 June 2007 14:50
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] initdb problem

acorn$ sudo su postgres -c 'pg_ctl start -l logfile -D
/Library/PostgreSQL8/data'
Password:
sh: line 1: logfile: Permission denied
server starting

acorn$ postgres
postgres cannot access the server configuration file
"/Library/PostgreSQL8/data:/postgresql.conf": No such file
or directory

Surely initdb should have created the configure file?


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] initdb problem

[Phillip Smith]

My bad - is has the shell, but as you say, the home directory is /dev/null

NOW, I must preface this with a warning saying that I have NOT tested this,
and do not guarantee that it will work. I found it on a Mac forum and have
never tried it, or seen confirmation that it will work. Follow on at your
own risk!

Apparently Mac stores all it's user info in something called Net Info (not
/etc/passwd unless in Single User Mode). To change the home directory of a
user (as best I can tell), you would need to execute this at a terminal
prompt:

niutil -destroyval -u admin . /users/postgres home /dev/null
niutil -appendprop -u admin . /users/postgres home /Library/postgreSQL8

Once again, use at your own risk! Do let us know if it worked or not.

Cheers,
~p


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] initdb problem

[Phillip Smith]

I'm guessing all the problems below are something to do with the 'postgres'
user not having a correct shell login setup. It looks like the home
directory is set to /dev/null which would explain all the "not a directory"
errors.

You'll need to edit the postgers user to have a proper login shell
(/bin/bash maybe?) but I specifically avoid Mac's so that's about all I can
tell you. B)

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of James Herbers
Sent: Wednesday, 20 June 2007 11:11
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] initdb problem

No MAC expert but try this when changing to the postgres user:
su - postgres
the hyphen gives you an environment for the postgres user.

J. Herbers

Quoting Joe Barwell <[EMAIL PROTECTED]>:

> Hello people,
>
> Having installed the universal binary for pg 8.2.4 on my mac
> running 10.3.9, I'm now trying to use the initdb command,
> but keep running into problems. I can't seem to get the
> right syntax. Can anyone suggest where I'm going wrong?
> Here's what terminal shows:
>
> Acorn acorn 1 acorn$ login root
> Password:
> Last login: Thu Jun 14 13:43:54 on ttyp1
> Welcome to Darwin!
> Acorn:~ root# su postgres
> shell-init: could not get current directory: getcwd: cannot
> access parent directories: Permission denied
> su: /dev/null/.bashrc: Not a directory
> Acorn:/var/root postgres$ initdb -D
> /Library/PostgreSQL8/data
> su: initdb: command not found
> Acorn:/var/root postgres$ cd
> chdir: could not get current directory: getcwd: cannot
> access parent directories: Permission denied
> su: cd: /dev/null: Not a directory
> Acorn:/var/root postgres$ cwd
> su: cwd: command not found
> Acorn:/var/root postgres$ /Library/PostgreSQL8/initdb -D
> /Library/PostgreSQL8/data
> su: /Library/PostgreSQL8/initdb: No such file or directory
> Acorn:/var/root postgres$ /Library/PostgreSQL8/bin/initdb -D
> /Library/PostgreSQL8/data
> could not identify current directory: Permission denied
> could not identify current directory: Permission denied
> could not identify current directory: Permission denied
> The program "postgres" is needed by initdb but was not found
> in the
> same directory as "initdb".
> Check your installation.
> Acorn:/var/root postgres$
>
> TIA,
>
> Joe
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] Encountering errors while using pg_ctl

[Phillip Smith]

> [EMAIL PROTECTED] pgsql]$ pg_ctl -D /usr/local/pgsql/data stop -l log
> The programs "postmaster" and "psql" are needed by pg_ctl but
> were not found in the directory "/usr/bin".
> Check your installation.

Where did you install Postgres to? Did you install a binary package or
compile from source?

You may just need to sym link postmaster and psql into /usr/bin/ from their
actual location - probably something like /usr/local/postgres/bin/

Cheers,
~p


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] Syntax in postgresql.conf

[Phillip Smith]

I believe you would need it to be:
listen_addresses = '192.168.0.1,localhost'

ie, Don't close the quotes between your CSV's

On Mon, 2007-05-28 at 11:11 +0200, Johann Spies wrote:

> listen_addresses = '192.168.0.1', 'localhost'
> 
> I get
> 
> $sudo /etc/init.d/postgresql-8.1 restart
>  * Restarting PostgreSQL 8.1 database server
>  * Error: Invalid line 53 in /etc/postgresql/8.1/main/postgresql.conf
> 
>  Why?


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [ADMIN] improve select performance...

[Phillip Smith]

Can you post the actual queries and the full EXPLAIN ANALYZE for the
queries?

Are you running 64bit Debian and PostgreSQL?

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Steve Holdoway
Sent: Wednesday, 16 May 2007 09:08
To: pgsql-admin@postgresql.org
Subject: [ADMIN] improve select performance...

Can anyone out there help me, I'm having a bit of a problem trying to
improve the performance of a php script that does a data load?

At the moment, the script checks a database to see if the entry is present,
and, if not, it adds it. The table has about 400,000 rows, and I'm checking
a bulk load of about 50,000 entries. The table is defined as follows (not my
design!):

Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

db=> \d badurls
 Table "public.badurls"
   Column|Type | Modifiers
-+-+
 url | character varying   | not null
 ip  | character(20)   |
 dateadded   | timestamp without time zone | not null
 type| character(1)|
 publishdate | timestamp without time zone |
 status  | integer | not null default 1
 version | integer | default 0
 edited  | integer | default 0
 category| character(4)[]  |
Indexes:
"idxbadurls_url" hash (url)
"idxbadurls_version" btree (version)


and I'm accessing the url column. (I've tried a btree index as well... not
much difference)

In an attempt to improve the performance, I'm getting a list of data that's
already there in a single hit, and not attempting to insert them ( I'm still
checking the rest first, just in case... ).

So, I'm trying to run a select url from badurls where url in (  ) to get
this list. However, this is also desperately slow...

1000 in the list: 14 sec
5000 in the list: 2:40
1 in the list: 5:50

( run from a client machine over a 100mbit lan ).

Anyway, that's enough to prove to me that I need to rejig stuff to get this
to work!!!

Server's a dual xeon machine, with hardware raid, and running debian. It's
got 4GB of memory, 1GB is currently not even used for io buffers.

The postgres instance is configured as standard, except for increasing the
shared_buffers to 200MB.

Explain plan for about 50 entries in the 'in' clause returns

Bitmap Heap Scan on badurls  (cost=4531.22..15748.67 rows=60462 width=32)
   Recheck Cond: ((url)::text = ANY (('{.}'::character
varying[])::text[]))
   ->  Bitmap Index Scan on idxbadurls_url  (cost=0.00..4516.10 rows=60462
width=0)
 Index Cond: ((url)::text = ANY (('{.}'::character
varying[])::text[]))
(4 rows)


Can anyone suggest what I should be reconfiguring???

Cheers,


Steve

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] Auto Back up in postgresql

[Phillip Smith]

As a very simple solution, add this to your crontab for an appropriate time
schedule (daily? hourly?)
pg_dumpall > ~/postgres_backup.sql

The documentation is a good place for information! You might like to look at
this chapter specifically:
http://www.postgresql.org/docs/current/static/backup.html

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Monday, 14 May 2007 14:26
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Auto Back up in postgresql

hi friends

if i delete any database of table data it automatically stored in backup
so i rollback it whenever i require for full server(including all database
and table)


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] Help me to decrypt password

[Phillip Smith]

Assuming the ERP software uses MD5 hashes to protect the password:

Since MD5 is a ONE-WAY encryption algorithm, then you *might* be able to
use a reverse lookup hash table such as the one found here:
http://gdataonline.com/seekhash.php

There may be similar hash tables around for other encryption methods.

On Tue, 2007-04-24 at 00:08 -0700, sufian khan wrote:
> Dear
> I am using an ERP application. I am administrator of that
> application.  Actually I want to see the users passwords those are
> stored in encryption format.  
> How I decrypt any password that is store in our database table
> app_users.



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [ADMIN] REINDEX using only 1 CPU (of 2)

[Phillip Smith]

A REINDEX is one process - you can't split one process over 2 CPU's.

If you start another process (a SELECT / UPDATE / INSERT for example), that
process (should) use the other processor.

Cheers,
~p


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Saturday, 14 April 2007 10:55
To: pgsql-admin@postgresql.org
Subject: [ADMIN] REINDEX using only 1 CPU (of 2)

Hi,

I've got PG 8.2.3 running on a dual-core P4, and I noticed that, at least
during reindexing, postgreSQL REINDEX process is using only 1 of those 2
cores.  You can clearly see this from this snippet from top:

Cpu0  : 93.3% us,  6.7% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu1  :  0.0% us,  2.6% sy,  0.0% ni, 85.5% id, 11.8% wa,  0.0% hi,  0.0% si


  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND

21909 pg  25   0  116m  89m  42m R 98.9  9.2   1:01.92 postgres: pg mydb
[local] REINDEX



This PG 8.2.3 was compiled from sources on this box, so if there is anything
in the config/install process that should pick up that this box has "2"
CPUs, perhaps that didn't happen?  I know I didn't manually instruct the
config/install that the box is a dual-core box.  This is running on a Linux
box with the SMP-aware kernel.

Is this a known limitation?  Am I reading this wrong?

Thanks,
Otis



---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] [SQL] setting up a mirroring or replication database

[Phillip Smith]

This may best be asked in the admin group - please don't cross-post. Let's
keep all further discussion in the admin group.

 

A good starting place for you would be the PostgreSQL manual, specifically
the chapter called "High Availability":

http://www.postgresql.org/docs/current/interactive/high-availability.html

. also Pgpool:

http://pgfoundry.org/projects/pgpool/

. and Slony:

http://slony.info/

 

Cheers,

~p

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Karthikeyan Sundaram
Sent: Friday, 13 April 2007 10:39
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [SQL] setting up a mirroring or replication database

 

Hi Team,
 
We are using Postgres 8.1.0 and in the plans to migrate to 8.2.3.   Ours
is a OLTP application.  Publishers, advertisers and consumers use our system
world wide.  Right now it's not a very big database. But we are expanding
our operations to Europe and US where we are expecting a moster growth.
 
I want to setup a mirroring database or replications database so that if
one database crash, the load balancing database should take care or it.  Not
only that, if I am upgrading one database, the other should take the load. 
 
I have never set this kind of replication or mirroring the database
before.  What are the steps and procedures to do this kind of setup.
 
   Please advise.
 
Regards
skarthi

 

  _  

Live Search Maps - find all the local information you need, right when you
need it. Find   it!



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [ADMIN] Best compressed archive_command on Linux?

[Phillip Smith]

I experience similar on my nightly backups - tar exits with status '2' but
for the life of me I cannot work out WHAT exit 2 means. It still works fine,
but the non-zero return it just annoying.

If you're just compressing (ie, not creating a tar-type archive, just either
GZip (gzip) or BZip2 (bzip2) the WAL files.

BZip2 has incredible compression, but at the expense of speed of course.

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Eduardo J. Ortega
Sent: Thursday, 12 April 2007 02:50
To: [EMAIL PROTECTED]
Subject: Re: [ADMIN] Best compressed archive_command on Linux?

sometimes tar  (gnu tar) exited with nonzero status for no reason on my 
system... no error msg or anything. I recommend something called star.

On Wednesday 11 April 2007, Sean Murphy wrote:
> I'm in the process of moving an 8.2 server from Win32 to Ubuntu Linux.
> On Win32, I implemented WAL archiving with the archive_command
> "rar a -ep -m5 -s -t -dh w:/%f.rar %p"
>
> Anyway, I'm going to use WAL archiving on the new server, and need the
> files to be compressed. My first inclination is to use
> "tar -czf /walarchive/%f.tar.gz %p > /dev/null"
>
> Before I go with that, though, I was wondering if anybody had any
> specific experience / advice with a better command, reasons why this
> command might fail zero or succeed nonzero, or additional/different
> options for tar that would yield better results.
>
> Thanks,
> Sean
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq



-- 
Eduardo J. Ortega - Linux user #222873 
"No fake - I'm a big fan of konqueror, and I use it for everything." --
Linus 
Torvalds

---(end of broadcast)---
TIP 6: explain analyze is your friend


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] Autostartup

[Phillip Smith]

Have a look in the /contrib/start-scripts/ directory in the source tree.
There's a Linux script in there that should do what you're after.

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alexander B.
Sent: Thursday, 29 March 2007 00:25
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Autostartup

Hi,

How can I configure to auto-startup Postgres installed on Open Suse 10.1??
On other systems, just configure file start.conf to "auto". But on Suse ...

Could you help me?
Thank you


___ 
Yahoo! Mail - Sempre a melhor opção para você! 
Experimente já e veja as novidades. 
http://br.yahoo.com/mailbeta/tudonovo/
 


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] set password doesn't work

[Phillip Smith]

Your pg_hba.conf file will be configured to allow TRUST (ie, no password)
authentication for the computers you are connecting from, most likely your
local network subnet such as 192.168.0.1/24

Have a look in there and see, if you need help, copy and paste the
pg_hba.conf settings (excluding comments) to the group for us.

Cheers,
~p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Sabin Coanda
Sent: Thursday, 22 March 2007 23:36
To: pgsql-admin@postgresql.org
Subject: [ADMIN] set password doesn't work

Hi,

I installed on linux the dbms "PostgreSQL 8.2.3 on i686-redhat-linux-gnu, 
compiled by GCC gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)".

Initially I set an empty password for the user postgres.

Some times later I connect from a different machine (Windows) to that 
database, and I set a password for postgres user, and now I see it in 
pgAdmin as:

CREATE ROLE postgres LOGIN
  ENCRYPTED PASSWORD 'md505a74e1678967a543fd92e711aa5762d'
  SUPERUSER INHERIT CREATEDB CREATEROLE;

Surprising I was able to connect now to that database server, without 
password, or specifying any text as password, both from the different 
machine, and from the same database server machine.

So then I tried to change the password by the following script:

ALTER USER postgres WITH ENCRYPTED PASSWORD '...';

But nothing change, and it has the same behaviour like an empty password.

What's wrong ? Please let me know if I applied the right procedure to set 
the password for postgres user.

TIA,
Sabin 



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] Vacuum taking forever

[Phillip Smith]

What are the specs of your machine? What is the memory settings in your
postgresql.conf file?

Cheers
~p

On Wed, 2007-03-21 at 19:42 -0700, Davidson, Robert wrote:
> df –h reports 806 MB free and 79 GB used. Is this enough free space to
> perform a vacuum? Should I export the table, drop it, and re-import
> it?


smime.p7s
Description: S/MIME cryptographic signature

Re: [ADMIN] how to create field auto increas value

[Phillip Smith]

Look at the "serial" data type in the documentation.
~p

On Tue, 2007-03-20 at 09:49 +0700, gunartha.nyoman wrote:
> Dear All,
>  
> I am trying to create table which have one field auto increas value
> like autonumber in ms access.
> My question is how to create this auto increas field.
>  
> Thank a lot for your attention
> 
> Regards,
> Nyoman/Bali
> 
>  
>  




smime.p7s
Description: S/MIME cryptographic signature

Re: [ADMIN] pg_hba.conf & putty

[Phillip Smith]

I know your problem (I think)

 

Your SSH forward you said is defined as:

Lserver.ip.address:5432

 

So the requests will be coming from your server's IP address via the eth0
interface. You need to change this to 127.0.0.1 to make it use the lo
interface, and then the requests will come from 127.0.0.1 to match your
pg_hba.conf

 

Cheers,

~p

 

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Moritz Bayer
Sent: Tuesday, 6 March 2007 10:58
To: Phillip Smith
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_hba.conf & putty

 

hello phillip,
 
yes, the connection i defined in pgadmin uses localhost:5432. 

Still, no success :(






2007/3/6, Phillip Smith < <mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED]>:

What address are you connecting to on your desktop computer? You should be
using localhost:5432 which putty will redirect to the server via the SSH
tunnel and the server will connect to PostgreSQL on your behalf.

 

~p

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:
<mailto:[EMAIL PROTECTED]>  [EMAIL PROTECTED]
On Behalf Of Moritz Bayer
Sent: Tuesday, 6 March 2007 10:31
To: pgsql-admin@postgresql.org
Subject: [ADMIN] pg_hba.conf & putty

 

dear list,

I know there are already hunderts of posts about pg_hba.conf, but still
after reading a few of them I still got troubles with this topic.

I've got a vpn server  with a ststic ip address, java apps are supposed to
run there receiving data from a postgresql database. 
The database is running, but for changes on it I want to open a putty
connection while using pgAdmin.
In putty, I define a tunnel to port 5432, its displayed like this:
Lserver.ip.address:5432

Thought this entries in my pg_hba.conf would do it: 

# "local" is for Unix domain socket connections only
local   all all   md5

# IPv4 local connections:
hostall all 127.0.0.1/32  md5

# IPv6 local connections:
hostall all ::1/128   md5

well, it doesn't. When i run  pgAdmin with an open  putty connection, it
still says: error connecting to server. 

Any hints how I can find out whats still wrong?

Thanks in advance,
Mo

 

***Confidentiality and Privilege Notice*** 

The material contained in this message is privileged and confidential to the
addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy or
deliver this message to anyone, and you should destroy it and kindly notify
the sender by reply email. 

Information in this message that does not relate to the official business of
Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments 

 



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [ADMIN] pg_hba.conf & putty

[Phillip Smith]

What address are you connecting to on your desktop computer? You should be
using localhost:5432 which putty will redirect to the server via the SSH
tunnel and the server will connect to PostgreSQL on your behalf.

 

~p

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Moritz Bayer
Sent: Tuesday, 6 March 2007 10:31
To: pgsql-admin@postgresql.org
Subject: [ADMIN] pg_hba.conf & putty

 

dear list,

I know there are already hunderts of posts about pg_hba.conf, but still
after reading a few of them I still got troubles with this topic.

I've got a vpn server  with a ststic ip address, java apps are supposed to
run there receiving data from a postgresql database. 
The database is running, but for changes on it I want to open a putty
connection while using pgAdmin.
In putty, I define a tunnel to port 5432, its displayed like this:
Lserver.ip.address:5432

Thought this entries in my pg_hba.conf would do it: 

# "local" is for Unix domain socket connections only
local   all all   md5

# IPv4 local connections:
hostall all 127.0.0.1/32  md5

# IPv6 local connections:
hostall all ::1/128   md5

well, it doesn't. When i run  pgAdmin with an open  putty connection, it
still says: error connecting to server. 

Any hints how I can find out whats still wrong?

Thanks in advance,
Mo



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

Re: [ADMIN] RPM based install

[Phillip Smith]

Download the RPM, run
rpm -Uvh 

I would recommend compiling from Source though. The documentation on the
PostgreSQL website is very good at explaining how to do this.

http://www.postgresql.org

Cheers,
-p


On Tue, 2007-02-06 at 17:35 -0800, Trula Thomas wrote:
> Hello,RPM based install how do I start, please help.
> 
> Thank you
> 
> 
Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, Vic, 3131
AUSTRALIA

P. +613 9845 0600
F. +613 9845 0655
E. [EMAIL PROTECTED]


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [ADMIN] how to add a new data partition

[Phillip Smith]

Google is your friend here... Try these:

http://www.ibm.com/developerworks/linux/library/l-lvm/
http://www.gweep.net/~sfoskett/linux/lvmlinux.html
http://tldp.org/HOWTO/LVM-HOWTO/
http://www.linuxdevcenter.com/pub/a/linux/2006/04/27/managing-disk-space-with-lvm.html
http://www.linuxhomenetworking.com/wiki/index.php/Quick_HOWTO_:_Ch27_:_Expanding_Disk_Capacity
http://www.gagme.com/greg/linux/raid-lvm.php



On Tue, 2007-02-06 at 16:20 -0800, Karthikeyan Sundaram wrote:
> Tom,
> 
>Thanks for your reply.  I am new to postgres. What is LVM? and how should 
> I use it.  Can you guide me?.
> 
> Regards
> skarthi
> 
> 
> >From: Tom Lane <[EMAIL PROTECTED]>
> >To: Phillip Smith <[EMAIL PROTECTED]>
> >CC: Karthikeyan Sundaram <[EMAIL PROTECTED]>, 
> >pgsql-admin@postgresql.org
> >Subject: Re: [ADMIN] how to add a new data partition Date: Tue, 06 Feb 2007 
> >17:59:51 -0500
> >
> >Phillip Smith <[EMAIL PROTECTED]> writes:
> > > Perhaps a LVM group would be appropriate in this situation if you have
> > > the luxury of being able to implement that somehow...
> >
> >Indeed, the existence of LVM and similar tools is exactly why we don't
> >deem it pressing to come up with Oracle-like tablespace management.
> >If you want a tablespace to span multiple physical volumes, use LVM.
> >The Postgres project tries to avoid re-inventing perfectly good wheels ...
> >
> > regards, tom lane
> >
> >---(end of broadcast)---
> >TIP 1: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to [EMAIL PROTECTED] so that your
> >message can get through to the mailing list cleanly
> 
> _
> Search for grocery stores. Find gratitude. Turn a simple search into 
> something more. 
> http://click4thecause.live.com/search/charity/default.aspx?source=hmemtagline_gratitude&FORM=WLMTAG
> 
> 


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [ADMIN] how to add a new data partition

[Phillip Smith]

No - As Tom Lane said: Why reinvent LVM?
-p


On Tue, 2007-02-06 at 16:09 -0800, Karthikeyan Sundaram wrote:
> Hi Scott,
> 
>Thanks for replying.  The tablespace is not the main problem here.  I 
> want the data files to grow in the new partition along with the old linux 
> partition.
> 
>Let me put like this.
> 
> We have a linux partition of 120GB called as 
> /local/postgres/database/Data/< followed by all files>
> 
>  This /local partition is 95 % used.
> 
>  So we are adding one more partition called /local1 which is 
> approximately 250 GB.  Now I want to inform postgres that after /local 
> partiion is 100 %, I want to utilize the /local1 so that the database can 
> grow significantly.  In other words, the data file created and maintained by 
> postgres will grow in the /local1 partition.
> 
> How to do this in postgres?
> 
> I don't want to compare with Oracle.  But in Oracle, when we create the 
> DB
> 
> we will tell alter database add data file 
> '/local/postgres/database/data'
> then if /local partition is full, we can add another data file  such as
> alter database  add data file '/local1/data2'
> 
> Is there a similar way or equivalent way to do in postgres?
> 
> Regards
> skarthi
> 
> 
> 
> >From: Scott Marlowe <[EMAIL PROTECTED]>
> >To: Karthikeyan Sundaram <[EMAIL PROTECTED]>
> >CC: pgsql-admin@postgresql.org
> >Subject: Re: [ADMIN] how to add a new data partition
> >Date: Tue, 06 Feb 2007 17:17:15 -0600
> >
> >On Tue, 2007-02-06 at 15:46, Karthikeyan Sundaram wrote:
> > > Hi all,
> > >
> > >   We are using postgres 8.0.1 on our production and 8.2 on our 
> >development.
> > > The linuix partition is getting full where the database resides.  In few
> > > weeks our partition will ran out of space so the database will also run 
> >out
> > > of space. We have added another HDD of 300GB.  Now my question is, how 
> >will
> > > I point the database to the new partition along with the old one. Do I 
> >need
> > > to create a symbolic link to the exitisting DB?
> > >
> > >Adding a new tablespace doesn't solve the issue as there are many 
> >tables
> > > in the old tablespace.  Is there a way to extend the tablespace to the 
> >new
> > > partition like in Oracle.
> >
> >So,
> >
> >alter table ... set tablespace new_tablespace
> >
> >doesn't work for you in this circumstance?
> >
> >---(end of broadcast)---
> >TIP 9: In versions below 8.0, the planner will ignore your desire to
> >choose an index scan if your joining column's datatypes do not
> >match
> 
> _
> Invite your Hotmail contacts to join your friends list with Windows Live 
> Spaces 
> http://clk.atdmt.com/MSN/go/msnnkwsp007001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us
> 
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] how to add a new data partition

[Phillip Smith]

AFAIK, Postgres doesn't support anything like this, apart from
table-spaces which you've already indicated aren't appropriate.

Perhaps a LVM group would be appropriate in this situation if you have
the luxury of being able to implement that somehow...

Cheers,
-p


On Tue, 2007-02-06 at 13:55 -0800, Karthikeyan Sundaram wrote:
> Hi Philip,
> 
>Thanks for the reply.  Our DB grows very fast.  Hence I don't want to 
> move the exisisting data dir.  If I move my data file to the new partition, 
> I will face the same issue in another month instead of week.
> 
> My requirement is to keep the old data directory along with the new 
> partition.
> 
>In Oracle, we can tell the alter database add data file '/new partition' 
> so the the new partition will take into effect along with the old one.
> 
>Is there a similar way in postgres?
> 
> 
> Regards
> skarthi
> 
> 
> >From: "Phillip Smith" <[EMAIL PROTECTED]>
> >To: "'Karthikeyan Sundaram'" 
> ><[EMAIL PROTECTED]>,
> >Subject: Re: [ADMIN] how to add a new data partition
> >Date: Wed, 7 Feb 2007 08:52:06 +1100
> >
> >One option if you can have the DB offline for a little while:
> >
> >1) Shutdown Postgres
> >2) Move your current data directory to the new partition
> >3) Update your PGDATA variable, and any scripts that reference it to 
> >reflect
> >the new data path. You might also like to symlink the old data dir to the
> >new one.
> >4) Restart Postgres. pg_ctl -D /new/path/to/data start
> >
> >That's worked for me several time before.
> >
> >Cheers,
> >-p
> >
> >-Original Message-
> >From: [EMAIL PROTECTED]
> >[mailto:[EMAIL PROTECTED] On Behalf Of Karthikeyan Sundaram
> >Sent: Wednesday, 7 February 2007 08:46
> >To: pgsql-admin@postgresql.org
> >Subject: [ADMIN] how to add a new data partition
> >
> >Hi all,
> >
> >   We are using postgres 8.0.1 on our production and 8.2 on our 
> >development.
> >
> >The linuix partition is getting full where the database resides.  In few
> >weeks our partition will ran out of space so the database will also run out
> >of space. We have added another HDD of 300GB.  Now my question is, how will
> >I point the database to the new partition along with the old one. Do I need
> >to create a symbolic link to the exitisting DB?
> >
> >Adding a new tablespace doesn't solve the issue as there are many 
> >tables
> >in the old tablespace.  Is there a way to extend the tablespace to the new
> >partition like in Oracle.
> >
> >   Did anybody faced this problem and how did you resolve.  Please share 
> >your
> >
> >thoughts.
> >
> >
> >Regards
> >skarthi
> >
> >_
> >Talk now to your Hotmail contacts with Windows Live Messenger.
> >http://get.live.com/messenger/overview
> >
> >
> >---(end of broadcast)---
> >TIP 4: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> >
> >
> >***Confidentiality and Privilege Notice***
> >
> >The material contained in this message is privileged and confidential to
> >the addressee.  If you are not the addressee indicated in this message or
> >responsible for delivery of the message to such person, you may not copy
> >or deliver this message to anyone, and you should destroy it and kindly
> >notify the sender by reply email.
> >
> >Information in this message that does not relate to the official business
> >of Weatherbeeta must be treated as neither given nor endorsed by 
> >Weatherbeeta.
> >Weatherbeeta, its employees, contractors or associates shall not be liable
> >for direct, indirect or consequential loss arising from transmission of 
> >this
> >message or any attachments
> >
> >---(end of broadcast)---
> >TIP 5: don't forget to increase your free space map settings
> 
> _
> Valentines Day -- Shop for gifts that spell L-O-V-E at MSN Shopping 
> http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptnrdata=24095&tcode=wlmtagline
> 
> 


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addresse

Re: [ADMIN] how to add a new data partition

[Phillip Smith]

One option if you can have the DB offline for a little while:

1) Shutdown Postgres
2) Move your current data directory to the new partition
3) Update your PGDATA variable, and any scripts that reference it to reflect
the new data path. You might also like to symlink the old data dir to the
new one.
4) Restart Postgres. pg_ctl -D /new/path/to/data start

That's worked for me several time before.

Cheers,
-p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Karthikeyan Sundaram
Sent: Wednesday, 7 February 2007 08:46
To: pgsql-admin@postgresql.org
Subject: [ADMIN] how to add a new data partition

Hi all,

  We are using postgres 8.0.1 on our production and 8.2 on our development.

The linuix partition is getting full where the database resides.  In few 
weeks our partition will ran out of space so the database will also run out 
of space. We have added another HDD of 300GB.  Now my question is, how will 
I point the database to the new partition along with the old one. Do I need 
to create a symbolic link to the exitisting DB?

   Adding a new tablespace doesn't solve the issue as there are many tables 
in the old tablespace.  Is there a way to extend the tablespace to the new 
partition like in Oracle.

  Did anybody faced this problem and how did you resolve.  Please share your

thoughts.


Regards
skarthi

_
Talk now to your Hotmail contacts with Windows Live Messenger. 
http://get.live.com/messenger/overview


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] Backup Strategies?

[Phillip Smith]

I actually dump to a local file, then include that in a TAR GZ file, so it's
not actually in plain text on the network, although not truly encrypted
either...

Cheers,
-p


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ray Stell
Sent: Wednesday, 7 February 2007 00:34
To: Phillip Smith
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Backup Strategies?

On Sat, Feb 03, 2007 at 08:28:48PM +1100, Phillip Smith wrote:
> 1) As part of a backup script, call pg_dumpall as the postgres *nix
> users and PG user, then pipe the output to a file on a remote server.

might consider adding scp to the mix so that the data is not in
the clear on the wire.  You can use the authorized_keys feature 
so that passwd is not needed in the script. 

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] Backup Strategies?

[Phillip Smith]

Have you looked at pg_dump and/or pg_dumpall? That would be the more
common way of performing backups.

I run 2 backup's nightly of our databases:
1) As part of a backup script, call pg_dumpall as the postgres *nix
users and PG user, then pipe the output to a file on a remote server.
2) As a cronjob: pg_dumpall > /dev/st0

Cheers,
-p


On Sat, 2007-02-03 at 01:51 -0500, Joshua Kramer wrote:
> Hello All,
> 
> What strategies are people using for automated, script-based backup of 
> databases?  There are a few I can think of:
> 
> 1. Create a "db_backup" unix user and a "db_backup" pgsql user.  Grant 
> READ access to all objects on all databases for the "db_backup" pgsql 
> user.  Create a .pgpass file in the home directory of the "db_backup" unix 
> user.  Backup as needed with a script run as the "db_backup" unix user.
> 
> 2. Create a "db_backup" unix user and repeat above, except using the 
> "postgres" db user.
> 
> Thanks,
> -Josh
> 
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN] Backup Strategies?

[Phillip Smith]

Have you looked at pg_dump and/or pg_dumpall? That would be the more
common way of performing backups.

I run 2 backup's nightly of our databases:
1) As part of a backup script, call pg_dumpall as the postgres *nix
users and PG user, then pipe the output to a file on a remote server.
2) As a cronjob: pg_dumpall > /dev/st0

Cheers,
-p


On Sat, 2007-02-03 at 01:51 -0500, Joshua Kramer wrote:
> Hello All,
> 
> What strategies are people using for automated, script-based backup of 
> databases?  There are a few I can think of:
> 
> 1. Create a "db_backup" unix user and a "db_backup" pgsql user.  Grant 
> READ access to all objects on all databases for the "db_backup" pgsql 
> user.  Create a .pgpass file in the home directory of the "db_backup" unix 
> user.  Backup as needed with a script run as the "db_backup" unix user.
> 
> 2. Create a "db_backup" unix user and repeat above, except using the 
> "postgres" db user.
> 
> Thanks,
> -Josh
> 
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] blobs

[Phillip Smith]

I don't know about your table question, but may I ask why you're running
32-bit when you have dual Xeon processors?

I have dual Xeon's in my DWH, and I used to run 32-bit which I upgraded to
64-bit over Christmas. We run a nightly import to that database which used
to take around 5 hours which now completes in less than 1 hour.

Many of our large queries also run much faster - the only thing I did was
reload the box with RedHat ES 4 64-bit instead of 32-bit.

My 2.2 cents (Aust. GST inclusive!)

Cheers,
-p

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Steve Holdoway
Sent: Thursday, 1 February 2007 08:46
To: pgsql-admin@postgresql.org
Subject: [ADMIN] blobs

I'm got the enviable task of redesigning an MySQL based project from
scratch. We need a proper rdbms for this project, and I want to use PG 8.2.

The table I'm concerned with at the moment have (currently) 5 million rows,
with a churn of about 300,000 rows a week. The table has about a million
hits a day, which makes it the main potential bottleneck in this database.

We need to store some large ( 0 -> 100kB ) data with each row. Would you
recommend adding it as columns in this table, given that blobs will be
stored in the pg_largeobject table anyway, or would you recommend a daughter
table for this?

Any other suggestions on how to avoid performance problems with this table (
hardware is dual Xeon, 4GB mem, 2 hardware raid channels for storage + 1 for
logs, all running debian 32 bit ).

Cheers,

Steve

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] Terminating a query that appears to be hung

[Phillip Smith]

Can youo be a bit more specific...? What client?

Psql should terminate the connection when it exist. pgAdmin will close it
when you exit the ap, or if you choose 'Disconnect' from the menu. In PHP
you'll need to call pg_close function... Every 'client' will have a
different method...

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert D Oden
Sent: Tuesday, 30 January 2007 2:03 AM
To: PgSQL ADMIN
Subject: [ADMIN] Terminating a query that appears to be hung

Is there a way to terminate a client session without having to restart 
Postgres.

Thanks

-- 

Robert D Oden
Database Marketing Technologies, Inc
951 Locust Hill Circle
Belton MO 64012-1786

Ph:  816-318-8840
Fax: 816-318-8841

[EMAIL PROTECTED]


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] How do you upgrade for production servers?

[Phillip Smith]

I install the new version to a new directory (eg, /usr/local/pg-8.2 ) then
start that server on a different port to the live server (eg, 54321), create
a new data directory (eg. /home/postgres/pgData-8.2) dump the live database,
import to the new database to make sure it's all good, then once I know
that's all good, I'll drop the live database back to "single user mode" using
pg_hba.conf, dump the database again, shutdown the old version, restart the
new version on the normal port, re-import the data then re-enable normal
access with pg_hba.conf

I have the advantage that our database generally isn't  accessed out of
 hours, so I have the luxury to be able to have the server offline for 30 -
 60 minutes while the second dump and import is happening. Depends how big
 your database is as to how long the server will be unavailable.

At least by installing the new version to a new directory and doing a test
import first lets me see how (if) the new version will affect the data
without having the live server offline.

Cheers,
-p

On Friday 29 December 2006 23:06, Arnau wrote:
> Hi all,
>
>This is a general question about which procedure you follow when you
> upgrade your productions servers. Let's say we have a server running and
> older version and I want to install the latest version on the same
> server. Do you install slony? do you dump all the databases, install the
> latest version and after you restore? any other procedure? I'd like to
> have my production server down as less time as possible.
>
>
> Thank you very much and have a great new year :D

---

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster