subject:"\[GENERAL\] starting"


On 02/18/2015 10:24 AM, Guillaume Drolet wrote:



2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
mailto:adrian.kla...@aklaver.com:





So is E:\ a network drive shared by both machines?


No, E:\ is a local drive on which I created a tablespace, in order to
have enough space for my database. In my current setup on the source
machine, PGDATA is in the default PGSQL installation on the OS disk so
space is limited. On the destination machine, PGDATA will be on a
different, larger disk than the OS disk.


So is there an E:\ drive available on the destination machine?





Anyway, in the end I want to move the database that's in that
tablespace
back to pg_default. I see two possibilities:

1) Moving it now, before taking the base backup, using ALTER
DATABASE
mydb SET TABLESPACE pg_default; Then I assume I should be able
to use -X
stream and plain format with pg_basebackup.

Or

2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup
with -X
stream and plain format, copy the tablespace from the source to the
destination machine. Create a new symbolic link in
data/pg_tblspc on the
new machine and point it to the copied tablespace.

Are these two approaches feasible?


I would say 1 would be more feasible then 2. If you use 2, delete
the symlink and do the backup, what happens with any dependencies
between objects in the default tablespace and the one you cut out?
Also the pg_basebackup will be taking a backup of one part of the
cluster at one point in time and the copy of the remote tablespace
will possibly be at another point in time. I do no see that ending well.


You're probably right about that. My understanding was that, since this
is a single-user database (at least for now) on my machine, if I wasn't
performing any query or task during the backup, then the problem you
mentioned would in fact not be a problem.


Except Postgres performs tasks behind the scenes, so changes are 
happening. There is also still the dependency issue.










 Thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Starting new cluster from base backup

2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/18/2015 10:24 AM, Guillaume Drolet wrote:



 2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:



 So is E:\ a network drive shared by both machines?


 No, E:\ is a local drive on which I created a tablespace, in order to
 have enough space for my database. In my current setup on the source
 machine, PGDATA is in the default PGSQL installation on the OS disk so
 space is limited. On the destination machine, PGDATA will be on a
 different, larger disk than the OS disk.


 So is there an E:\ drive available on the destination machine?


Yes there is an E:\ drive available on the destination machine. But for
now, these two machines don't communicate. I take the backup on a hot swap
disk (F:\) and then swap it into the destination machine. Ultimately when
my database will be running on the destination machine, I'll connect to it
from other machines in my local network.






 Anyway, in the end I want to move the database that's in that
 tablespace
 back to pg_default. I see two possibilities:

 1) Moving it now, before taking the base backup, using ALTER
 DATABASE
 mydb SET TABLESPACE pg_default; Then I assume I should be able
 to use -X
 stream and plain format with pg_basebackup.

 Or

 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup
 with -X
 stream and plain format, copy the tablespace from the source to
 the
 destination machine. Create a new symbolic link in
 data/pg_tblspc on the
 new machine and point it to the copied tablespace.

 Are these two approaches feasible?


 I would say 1 would be more feasible then 2. If you use 2, delete
 the symlink and do the backup, what happens with any dependencies
 between objects in the default tablespace and the one you cut out?
 Also the pg_basebackup will be taking a backup of one part of the
 cluster at one point in time and the copy of the remote tablespace
 will possibly be at another point in time. I do no see that ending
 well.


 You're probably right about that. My understanding was that, since this
 is a single-user database (at least for now) on my machine, if I wasn't
 performing any query or task during the backup, then the problem you
 mentioned would in fact not be a problem.


 Except Postgres performs tasks behind the scenes, so changes are
 happening. There is also still the dependency issue.


Can't the dependency issue be fixed by creating a new junction in
data/pg_tblspc that would point to the relocated tablespace?










  Thanks.



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

2015-02-18 11:06 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/18/2015 04:26 AM, Guillaume Drolet wrote:



 2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:


 On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

 Adrian: thanks for this information.

 I tried running pg_basebackup in plain format with option -X
 stream
 (pg_basebackup -D F:\208376PT\db -X stream -l
 208376PT17022015 -U
 postgres -P) but I got the message:

 pg_basebackup: directory E:\Data\Database exists but is not
 empty

 I creatde a tablespace using CREATE TABLESPACE at the location
 mentioned
 in the message. According to what I read online about this, this
 message
 is issued when a tablespace was created under PGDATA. In my
 case, only
 the directory junction pointing to my tablespace (on a different
 drive
 than PGDATA) exists under PGDATA, not the tablespace itself.

 The only way I can run pg_basebackup with WAL files is with
 option -Ft
 and -X fetch. I'd much prefer using plain mode since my 670 GB
 tablespace takes a lot of time to extract when tarred. Is there
 another
 way to approach this?


 All I can come up with at the moment

 So what is the path on the original machine and can it be replicated
 on the new machine, at least temporarily?


 The path on the original (i.e. source) machine is:
 E:\Data\Database\PG_9.3_201306121\..


 I'm thinking if the path can be replicated, let pg_basebackup write
 to it and then create the tablespace you want and do ALTER TABLE SET
 TABLESPACE to move the tables. You would also need to do this for
 indexes.


 Not sure I understand when you say let pg_basebackup write to it. This
 tablespace already exists on the source machine so cannot be written
 over. It needs to be written in the backup so that I can than recreate
 it on the destination machine.


 So is E:\ a network drive shared by both machines?


No, E:\ is a local drive on which I created a tablespace, in order to have
enough space for my database. In my current setup on the source machine,
PGDATA is in the default PGSQL installation on the OS disk so space is
limited. On the destination machine, PGDATA will be on a different, larger
disk than the OS disk.



 Anyway, in the end I want to move the database that's in that tablespace
 back to pg_default. I see two possibilities:

 1) Moving it now, before taking the base backup, using ALTER DATABASE
 mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X
 stream and plain format with pg_basebackup.

 Or

 2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X
 stream and plain format, copy the tablespace from the source to the
 destination machine. Create a new symbolic link in data/pg_tblspc on the
 new machine and point it to the copied tablespace.

 Are these two approaches feasible?


 I would say 1 would be more feasible then 2. If you use 2, delete the
 symlink and do the backup, what happens with any dependencies between
 objects in the default tablespace and the one you cut out? Also the
 pg_basebackup will be taking a backup of one part of the cluster at one
 point in time and the copy of the remote tablespace will possibly be at
 another point in time. I do no see that ending well.


You're probably right about that. My understanding was that, since this is
a single-user database (at least for now) on my machine, if I wasn't
performing any query or task during the backup, then the problem you
mentioned would in fact not be a problem.







 Thanks.




 --
 Adrian Klaver
 adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

2015-02-18 16:11 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/18/2015 11:51 AM, Guillaume Drolet wrote:



 2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
 mailto:adrian.kla...@aklaver.com:

 On 02/18/2015 10:24 AM, Guillaume Drolet wrote:



 2015-02-18 11:06 GMT-05:00 Adrian Klaver
 adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com
 mailto:adrian.klaver@aklaver.__com
 mailto:adrian.kla...@aklaver.com:



  So is E:\ a network drive shared by both machines?


 No, E:\ is a local drive on which I created a tablespace, in
 order to
 have enough space for my database. In my current setup on the
 source
 machine, PGDATA is in the default PGSQL installation on the OS
 disk so
 space is limited. On the destination machine, PGDATA will be on a
 different, larger disk than the OS disk.


 So is there an E:\ drive available on the destination machine?


 Yes there is an E:\ drive available on the destination machine. But for
 now, these two machines don't communicate. I take the backup on a hot
 swap disk (F:\) and then swap it into the destination machine.
 Ultimately when my database will be running on the destination machine,
 I'll connect to it from other machines in my local network.


 So if I understand correctly you have:

 1) On source machine a directory E:\Data\Database.
 2) On the source machine in Postgres you have a created a tablespace that
 points at E:\Data\Database.
 3) On destination machine you have an E:\ drive also.

 You're correct


 Then have you tried:

 1) Create \Data\Database directory under E:\ on the destination machine.

2) Do the pg_basebackup.


I'm not sure I understand why, at this moment in the sequence of operation,
I would create \Data\Database under E:\ on the destination machine.
pg_basebackup, when run on the source DB on the source machine, has no idea
about the destination machine. Maybe you're confused with the F:\ drive,
which is the drive on which I tried to save my base backup with the command:

pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P

This drive (F:\) is not the destination machine, it's a swappable disk I
use to move my base backup from one machine (the source) to another (the
destination).




 Can't the dependency issue be fixed by creating a new junction in
 data/pg_tblspc that would point to the relocated tablespace?


 The docs say you can:

 http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html

 The directory $PGDATA/pg_tblspc contains symbolic links that point to
 each of the non-built-in tablespaces defined in the cluster. Although not
 recommended, it is possible to adjust the tablespace layout by hand by
 redefining these links. Under no circumstances perform this operation while
 the server is running. Note that in PostgreSQL 9.1 and earlier you will
 also need to update the pg_tablespace catalog with the new locations. (If
 you do not, pg_dump will continue to output the old tablespace locations.)


 I have not done it and I see the Although not recommended.. part above,
 so I would say that is a last resort solution.


I confirm this method works. I've done it in the past using the steps in
this blog and its comments:

http://www.databasesoup.com/2013/11/moving-tablespaces.html













   Thanks.



 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup


On 02/18/2015 11:51 AM, Guillaume Drolet wrote:



2015-02-18 13:40 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
mailto:adrian.kla...@aklaver.com:

On 02/18/2015 10:24 AM, Guillaume Drolet wrote:



2015-02-18 11:06 GMT-05:00 Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com
mailto:adrian.klaver@aklaver.__com
mailto:adrian.kla...@aklaver.com:



 So is E:\ a network drive shared by both machines?


No, E:\ is a local drive on which I created a tablespace, in
order to
have enough space for my database. In my current setup on the source
machine, PGDATA is in the default PGSQL installation on the OS
disk so
space is limited. On the destination machine, PGDATA will be on a
different, larger disk than the OS disk.


So is there an E:\ drive available on the destination machine?


Yes there is an E:\ drive available on the destination machine. But for
now, these two machines don't communicate. I take the backup on a hot
swap disk (F:\) and then swap it into the destination machine.
Ultimately when my database will be running on the destination machine,
I'll connect to it from other machines in my local network.


So if I understand correctly you have:

1) On source machine a directory E:\Data\Database.
2) On the source machine in Postgres you have a created a tablespace 
that points at E:\Data\Database.

3) On destination machine you have an E:\ drive also.

Then have you tried:

1) Create \Data\Database directory under E:\ on the destination machine.
2) Do the pg_basebackup.





Can't the dependency issue be fixed by creating a new junction in
data/pg_tblspc that would point to the relocated tablespace?


The docs say you can:

http://www.postgresql.org/docs/9.3/static/manage-ag-tablespaces.html

The directory $PGDATA/pg_tblspc contains symbolic links that point to 
each of the non-built-in tablespaces defined in the cluster. Although 
not recommended, it is possible to adjust the tablespace layout by hand 
by redefining these links. Under no circumstances perform this operation 
while the server is running. Note that in PostgreSQL 9.1 and earlier you 
will also need to update the pg_tablespace catalog with the new 
locations. (If you do not, pg_dump will continue to output the old 
tablespace locations.)



I have not done it and I see the Although not recommended.. part 
above, so I would say that is a last resort solution.













  Thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

 Adrian: thanks for this information.

 I tried running pg_basebackup in plain format with option -X stream
 (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
 postgres -P) but I got the message:

 pg_basebackup: directory E:\Data\Database exists but is not empty

 I creatde a tablespace using CREATE TABLESPACE at the location mentioned
 in the message. According to what I read online about this, this message
 is issued when a tablespace was created under PGDATA. In my case, only
 the directory junction pointing to my tablespace (on a different drive
 than PGDATA) exists under PGDATA, not the tablespace itself.

 The only way I can run pg_basebackup with WAL files is with option -Ft
 and -X fetch. I'd much prefer using plain mode since my 670 GB
 tablespace takes a lot of time to extract when tarred. Is there another
 way to approach this?


 All I can come up with at the moment

 So what is the path on the original machine and can it be replicated on
 the new machine, at least temporarily?


The path on the original (i.e. source) machine is: E:\Data\Database\
PG_9.3_201306121\..


 I'm thinking if the path can be replicated, let pg_basebackup write to it
 and then create the tablespace you want and do ALTER TABLE SET TABLESPACE
 to move the tables. You would also need to do this for indexes.


Not sure I understand when you say let pg_basebackup write to it. This
tablespace already exists on the source machine so cannot be written over.
It needs to be written in the backup so that I can than recreate it on the
destination machine.

Anyway, in the end I want to move the database that's in that tablespace
back to pg_default. I see two possibilities:

1) Moving it now, before taking the base backup, using ALTER DATABASE mydb
SET TABLESPACE pg_default; Then I assume I should be able to use -X stream
and plain format with pg_basebackup.

Or

2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X
stream and plain format, copy the tablespace from the source to the
destination machine. Create a new symbolic link in data/pg_tblspc on the
new machine and point it to the copied tablespace.

Are these two approaches feasible?





 Thanks.




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup


On 02/18/2015 04:26 AM, Guillaume Drolet wrote:



2015-02-17 17:14 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com
mailto:adrian.kla...@aklaver.com:

On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

Adrian: thanks for this information.

I tried running pg_basebackup in plain format with option -X stream
(pg_basebackup -D F:\208376PT\db -X stream -l
208376PT17022015 -U
postgres -P) but I got the message:

pg_basebackup: directory E:\Data\Database exists but is not empty

I creatde a tablespace using CREATE TABLESPACE at the location
mentioned
in the message. According to what I read online about this, this
message
is issued when a tablespace was created under PGDATA. In my
case, only
the directory junction pointing to my tablespace (on a different
drive
than PGDATA) exists under PGDATA, not the tablespace itself.

The only way I can run pg_basebackup with WAL files is with
option -Ft
and -X fetch. I'd much prefer using plain mode since my 670 GB
tablespace takes a lot of time to extract when tarred. Is there
another
way to approach this?


All I can come up with at the moment

So what is the path on the original machine and can it be replicated
on the new machine, at least temporarily?


The path on the original (i.e. source) machine is:
E:\Data\Database\PG_9.3_201306121\..


I'm thinking if the path can be replicated, let pg_basebackup write
to it and then create the tablespace you want and do ALTER TABLE SET
TABLESPACE to move the tables. You would also need to do this for
indexes.


Not sure I understand when you say let pg_basebackup write to it. This
tablespace already exists on the source machine so cannot be written
over. It needs to be written in the backup so that I can than recreate
it on the destination machine.


So is E:\ a network drive shared by both machines?



Anyway, in the end I want to move the database that's in that tablespace
back to pg_default. I see two possibilities:

1) Moving it now, before taking the base backup, using ALTER DATABASE
mydb SET TABLESPACE pg_default; Then I assume I should be able to use -X
stream and plain format with pg_basebackup.

Or

2) Delete the symbolic link in data/pg_tblspc, use pg_basebackup with -X
stream and plain format, copy the tablespace from the source to the
destination machine. Create a new symbolic link in data/pg_tblspc on the
new machine and point it to the copied tablespace.

Are these two approaches feasible?


I would say 1 would be more feasible then 2. If you use 2, delete the 
symlink and do the backup, what happens with any dependencies between 
objects in the default tablespace and the one you cut out? Also the 
pg_basebackup will be taking a backup of one part of the cluster at one 
point in time and the copy of the remote tablespace will possibly be at 
another point in time. I do no see that ending well.








Thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com





--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Starting new cluster from base backup


On 02/18/2015 01:48 PM, Guillaume Drolet wrote:








So if I understand correctly you have:

1) On source machine a directory E:\Data\Database.
2) On the source machine in Postgres you have a created a tablespace
that points at E:\Data\Database.
3) On destination machine you have an E:\ drive also.

You're correct

Then have you tried:

1) Create \Data\Database directory under E:\ on the destination machine.

2) Do the pg_basebackup.


I'm not sure I understand why, at this moment in the sequence of
operation, I would create \Data\Database under E:\ on the destination
machine.
pg_basebackup, when run on the source DB on the source machine, has no
idea about the destination machine. Maybe you're confused with the F:\
drive, which is the drive on which I tried to save my base backup with
the command:


I am confused, but not about F:\ drive:). My confusion was on where the 
error directory E:\Data\Database exists but is not empty occurred. I 
just ran a test. So the issue is in plain mode pg_basebackup does the 
binary copy to F:\208376PT\db which is fine. The problem is that it can
still see E:\Data\Database on the source machine, so when it tries to 
set up the copy of the tablespace it sees that the directory is not 
empty and stops. So the only way this going to work in 9.3 with plain is 
to copy not to F:\ but to the destination machine directly. I am 
guessing that is not possible?  It works in the tar case because the 
tablespace directory gets renamed.




pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P

This drive (F:\) is not the destination machine, it's a swappable disk I
use to move my base backup from one machine (the source) to another (the
destination).





I have not done it and I see the Although not recommended.. part
above, so I would say that is a last resort solution.


I confirm this method works. I've done it in the past using the steps in
this blog and its comments:

http://www.databasesoup.com/2013/11/moving-tablespaces.html


Interesting post, I missed it the first time around. Seems worth a try.








--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Guillaume Drolet

Adrian: thanks for this information.

I tried running pg_basebackup in plain format with option -X stream
(pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P) but I got the message:

pg_basebackup: directory E:\Data\Database exists but is not empty

I creatde a tablespace using CREATE TABLESPACE at the location mentioned in
the message. According to what I read online about this, this message is
issued when a tablespace was created under PGDATA. In my case, only the
directory junction pointing to my tablespace (on a different drive than
PGDATA) exists under PGDATA, not the tablespace itself.

The only way I can run pg_basebackup with WAL files is with option -Ft and
-X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes
a lot of time to extract when tarred. Is there another way to approach
this?

Thanks.



2015-02-16 15:21 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/16/2015 11:31 AM, Guillaume Drolet wrote:

 Dear listers,

 I want to move a cluster from one machine to another. I used
 pg_basebackup to create an archive and copied/extracted it over the old
 PGDATA location on the new machine (the server was stopped). If I start
 pgsql I get these messages in my log file:

 2015-02-16 14:29:12 EST LOG:  database system was interrupted; last
 known up at 2015-02-07 06:31:41 EST
 2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
 2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint
 record
 2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup,
 try removing the file E:/data/backup_label.
 2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with
 exit code 1
 2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
 failure

 I assume this is due to the fact the pg_xlog folder is empty (this is
 how pg_basebackup makes it in the archive) and that I haven't supplied a
 recovery.conf file with the restore restore_command = 'copy
 E:\\archivedir\\%f %p', and the archived WAL files.

 Now my question is: it this a correct way of moving a cluster between
 machines?

 If yes, what WAL files will I put in pg_xlog? Would I have needed to
 copy those that were in the old machine right after the base backup?

 If this is not the right way to do it, what is the best way?


 http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html

 
 -X method
 --xlog-method=method

 Includes the required transaction log files (WAL files) in the backup.
 This will include all transaction logs generated during the backup. If this
 option is specified, it is possible to start a postmaster directly in the
 extracted directory without the need to consult the log archive, thus
 making this a completely standalone backup 
 

 There is more under -X, so I would read the whole section.


 Thanks a lot for your help,

 Guillaume




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Guillaume Drolet

This provides part of the answer to my previous post, from the 9.4 doc
(although I'm running 9.3 but I guess the second phrase in the paragraph
applies to my case):

Tablespaces will in plain format by default be backed up to the same path
they have on the server, unless the option --tablespace-mapping is used.
Without this option, running a plain format base backup on the same host as
the server will not work if tablespaces are in use, because the backup
would have to be written to the same directory locations as the original
tablespaces.

I know the -T option is not available in 9.3. Is there another way to
circumvent the problem and still be able to backup using -X stream and
plain format when tablespace have been created elsewhere?

Thanks!

2015-02-17 9:54 GMT-05:00 Guillaume Drolet droletguilla...@gmail.com:

 Adrian: thanks for this information.

 I tried running pg_basebackup in plain format with option -X stream
 (pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
 postgres -P) but I got the message:

 pg_basebackup: directory E:\Data\Database exists but is not empty

 I creatde a tablespace using CREATE TABLESPACE at the location mentioned
 in the message. According to what I read online about this, this message is
 issued when a tablespace was created under PGDATA. In my case, only the
 directory junction pointing to my tablespace (on a different drive than
 PGDATA) exists under PGDATA, not the tablespace itself.

 The only way I can run pg_basebackup with WAL files is with option -Ft and
 -X fetch. I'd much prefer using plain mode since my 670 GB tablespace takes
 a lot of time to extract when tarred. Is there another way to approach
 this?

 Thanks.



 2015-02-16 15:21 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:

 On 02/16/2015 11:31 AM, Guillaume Drolet wrote:

 Dear listers,

 I want to move a cluster from one machine to another. I used
 pg_basebackup to create an archive and copied/extracted it over the old
 PGDATA location on the new machine (the server was stopped). If I start
 pgsql I get these messages in my log file:

 2015-02-16 14:29:12 EST LOG:  database system was interrupted; last
 known up at 2015-02-07 06:31:41 EST
 2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
 2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint
 record
 2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup,
 try removing the file E:/data/backup_label.
 2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with
 exit code 1
 2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
 failure

 I assume this is due to the fact the pg_xlog folder is empty (this is
 how pg_basebackup makes it in the archive) and that I haven't supplied a
 recovery.conf file with the restore restore_command = 'copy
 E:\\archivedir\\%f %p', and the archived WAL files.

 Now my question is: it this a correct way of moving a cluster between
 machines?

 If yes, what WAL files will I put in pg_xlog? Would I have needed to
 copy those that were in the old machine right after the base backup?

 If this is not the right way to do it, what is the best way?


 http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html

 
 -X method
 --xlog-method=method

 Includes the required transaction log files (WAL files) in the
 backup. This will include all transaction logs generated during the backup.
 If this option is specified, it is possible to start a postmaster directly
 in the extracted directory without the need to consult the log archive,
 thus making this a completely standalone backup 
 

 There is more under -X, so I would read the whole section.


 Thanks a lot for your help,

 Guillaume




 --
 Adrian Klaver
 adrian.kla...@aklaver.com

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Adrian Klaver


On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

Adrian: thanks for this information.

I tried running pg_basebackup in plain format with option -X stream
(pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P) but I got the message:

pg_basebackup: directory E:\Data\Database exists but is not empty

I creatde a tablespace using CREATE TABLESPACE at the location mentioned
in the message. According to what I read online about this, this message
is issued when a tablespace was created under PGDATA. In my case, only
the directory junction pointing to my tablespace (on a different drive
than PGDATA) exists under PGDATA, not the tablespace itself.


I believe all pg_basebackup cares about is whether the directory or not. 
It does not do any sort of further investigation to determine what is in 
the directory.




The only way I can run pg_basebackup with WAL files is with option -Ft
and -X fetch. I'd much prefer using plain mode since my 670 GB
tablespace takes a lot of time to extract when tarred. Is there another
way to approach this?

Thanks.






--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Starting new cluster from base backup

2015-02-17 Thread Adrian Klaver


On 02/17/2015 06:54 AM, Guillaume Drolet wrote:

Adrian: thanks for this information.

I tried running pg_basebackup in plain format with option -X stream
(pg_basebackup -D F:\208376PT\db -X stream -l 208376PT17022015 -U
postgres -P) but I got the message:

pg_basebackup: directory E:\Data\Database exists but is not empty

I creatde a tablespace using CREATE TABLESPACE at the location mentioned
in the message. According to what I read online about this, this message
is issued when a tablespace was created under PGDATA. In my case, only
the directory junction pointing to my tablespace (on a different drive
than PGDATA) exists under PGDATA, not the tablespace itself.

The only way I can run pg_basebackup with WAL files is with option -Ft
and -X fetch. I'd much prefer using plain mode since my 670 GB
tablespace takes a lot of time to extract when tarred. Is there another
way to approach this?


All I can come up with at the moment:

So what is the path on the original machine and can it be replicated on 
the new machine, at least temporarily?


I'm thinking if the path can be replicated, let pg_basebackup write to 
it and then create the tablespace you want and do ALTER TABLE SET 
TABLESPACE to move the tables. You would also need to do this for indexes.




Thanks.





--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Starting new cluster from base backup

2015-02-16 Thread Adrian Klaver


On 02/16/2015 11:31 AM, Guillaume Drolet wrote:

Dear listers,

I want to move a cluster from one machine to another. I used
pg_basebackup to create an archive and copied/extracted it over the old
PGDATA location on the new machine (the server was stopped). If I start
pgsql I get these messages in my log file:

2015-02-16 14:29:12 EST LOG:  database system was interrupted; last
known up at 2015-02-07 06:31:41 EST
2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint record
2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup,
try removing the file E:/data/backup_label.
2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with
exit code 1
2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
failure

I assume this is due to the fact the pg_xlog folder is empty (this is
how pg_basebackup makes it in the archive) and that I haven't supplied a
recovery.conf file with the restore restore_command = 'copy
E:\\archivedir\\%f %p', and the archived WAL files.

Now my question is: it this a correct way of moving a cluster between
machines?

If yes, what WAL files will I put in pg_xlog? Would I have needed to
copy those that were in the old machine right after the base backup?

If this is not the right way to do it, what is the best way?


http://www.postgresql.org/docs/9.3/interactive/app-pgbasebackup.html


-X method
--xlog-method=method

Includes the required transaction log files (WAL files) in the 
backup. This will include all transaction logs generated during the 
backup. If this option is specified, it is possible to start a 
postmaster directly in the extracted directory without the need to 
consult the log archive, thus making this a completely standalone backup 




There is more under -X, so I would read the whole section.


Thanks a lot for your help,

Guillaume





--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Starting new cluster from base backup

2015-02-16 Thread Guillaume Drolet

Dear listers,

I want to move a cluster from one machine to another. I used pg_basebackup
to create an archive and copied/extracted it over the old PGDATA location
on the new machine (the server was stopped). If I start pgsql I get these
messages in my log file:

2015-02-16 14:29:12 EST LOG:  database system was interrupted; last known
up at 2015-02-07 06:31:41 EST
2015-02-16 14:29:12 EST LOG:  invalid checkpoint record
2015-02-16 14:29:12 EST FATAL:  could not locate required checkpoint record
2015-02-16 14:29:12 EST HINT:  If you are not restoring from a backup, try
removing the file E:/data/backup_label.
2015-02-16 14:29:12 EST LOG:  startup process (PID 3148) exited with exit
code 1
2015-02-16 14:29:12 EST LOG:  aborting startup due to startup process
failure

I assume this is due to the fact the pg_xlog folder is empty (this is how
pg_basebackup makes it in the archive) and that I haven't supplied a
recovery.conf file with the restore restore_command = 'copy
E:\\archivedir\\%f %p', and the archived WAL files.

Now my question is: it this a correct way of moving a cluster between
machines?

If yes, what WAL files will I put in pg_xlog? Would I have needed to copy
those that were in the old machine right after the base backup?

If this is not the right way to do it, what is the best way?

Thanks a lot for your help,

Guillaume

Re: [GENERAL] Starting a cluster as a service

2012-06-29 Thread Léa Massiot

Hello Raghavendra,
Sorry for the late answer, I couldn't test this properly before.
That indeed solves the problem.
Thank you and best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5714759.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Starting a cluster as a service

2012-06-22 Thread Léa Massiot

Hi again,

I'm running PostgreSQL 9.1 under Windows XP.
I'm still trying to set a proper logging system for a PostgreSQL cluster
a_pg_cluster.

In the cluster postgresql.conf configuration file, I uncommented
logging_connector = on.
When I stopped + started the service, I could see a pg_log directory had
been created with a log file inside.

Now, I would like:
1) to store the cluster log files somewhere else say, for example:
C:\postgresql\logs\a_pg_cluster\
2) to have several small log files instead of a big one and log rotation.

Yet, when I set the log_directory parameter in the following way:
log_directory = 'C:\postgresql\logs\a_pg_cluster\'
and stop + start the service, it fails with always the same unusable
message:
The a_pgcluster_srv service on Local Computer started and then stopped.
Some services stop automatically if they have no work to do, for example,
the Performance Logs and Alerts service.

In the manual, one can read:
When logging_collector is enabled, this parameter determines the directory
in which log files will be created. It can be specified as an absolute path,
or relative to the cluster data directory. This parameter can only be set in
the postgresql.conf file or on the server command line.

So, what's wrong with it?

I would be happy with the following configuration:

logging_collector = on
log_directory = C:\postgresql\logs\a_pg_cluster\
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 10MB
log_min_messages = PANIC
log_error_verbosity = VERBOSE

except, it doesn't work... and I'm not sure about the levels PANIC and
VERBOSE.

Can you help?

Thank you and best regards.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713888.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Starting a cluster as a service

2012-06-22 Thread Raghavendra

On Fri, Jun 22, 2012 at 5:36 PM, Léa Massiot lmhe...@orange.fr wrote:

 Hi again,

 I'm running PostgreSQL 9.1 under Windows XP.
 I'm still trying to set a proper logging system for a PostgreSQL cluster
 a_pg_cluster.

 In the cluster postgresql.conf configuration file, I uncommented
 logging_connector = on.
 When I stopped + started the service, I could see a pg_log directory had
 been created with a log file inside.

 Now, I would like:
 1) to store the cluster log files somewhere else say, for example:
 C:\postgresql\logs\a_pg_cluster\
 2) to have several small log files instead of a big one and log rotation.

 Yet, when I set the log_directory parameter in the following way:
 log_directory = 'C:\postgresql\logs\a_pg_cluster\'

and stop + start the service, it fails with always the same unusable
 message:
 The a_pgcluster_srv service on Local Computer started and then stopped.
 Some services stop automatically if they have no work to do, for example,
 the Performance Logs and Alerts service.

 In the manual, one can read:
 When logging_collector is enabled, this parameter determines the directory
 in which log files will be created. It can be specified as an absolute
 path,
 or relative to the cluster data directory. This parameter can only be set
 in
 the postgresql.conf file or on the server command line.

 So, what's wrong with it?

 I would be happy with the following configuration:

 logging_collector = on
 log_directory = C:\postgresql\logs\a_pg_cluster\
 log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
 log_rotation_size = 10MB
 log_min_messages = PANIC
 log_error_verbosity = VERBOSE

 except, it doesn't work... and I'm not sure about the levels PANIC and
 VERBOSE.

 Can you help?

 Here two thing's, One you should have full access on the directory for
Postgres User.
Second, you need to give directory name as below.

log_directory = 'C:\\postgresql\\logs\\a_pg_cluster'


---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] Starting a cluster as a service

2012-06-18 Thread Léa Massiot

Hello Thomas,
Contrary to what you say, I provided command lines and messages (in case of
failure).
What is missing according to you?
Thank you and best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713039.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Starting a cluster as a service

2012-06-18 Thread Léa Massiot

Hi again,

It looks like the problem comes from the -l option I'm trying to set when
I register the service.

1) If I register the cluster as a service in the following way:

It works: the service is automatically started properly.

2) If I add the following option:

and then try to start the service (via Control Panel - Administrative
Tools - Services), it fails with the message:


Is this not the right way to set the cluster log file?
Or maybe this should be set in the cluster postgresql.conf configuration
file instead?

Thanks for helping.
Best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713066.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Starting a cluster as a service

2012-06-18 Thread Léa Massiot

Sorry. I added some  raw  tags so maybe this is the reason why you
couldn't see half of my message.

 

Hi again,

It looks like the problem comes from the -l option I'm trying to set when
I register the service.

1) If I register the cluster as a service in the following way:

---
dos ; a_user pg_ctl.exe register -N a_pgcluster_srv -U a_user -P a_password
-D a_pgcluster -S auto -w -t 60
---

It works: the service is automatically started properly.

2) If I add the following option:

---
-o -l 'path_to_the_cluster_log_file/a_pgcluster.log'
---

and then try to start the service (via Control Panel - Administrative
Tools - Services), it fails with the message:

---
The a_pgcluster_srv service on Local Computer started and then stopped. 
Some services stop automatically if they have no work to do, for example,
the Performance Logs and Alerts service.
---


Is this not the right way to set the cluster log file?
Or maybe this should be set in the cluster postgresql.conf configuration
file instead?

Thanks for helping.
Best regards.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713070.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[GENERAL] Starting a cluster as a service

2012-06-15 Thread Léa Massiot

Hello and thank you for reading my post.

My problem is that I do not manage to start a PostgreSQL cluster as a
Windows service.
The OS is WinXP.

- I've created a PostgreSQL cluster a_pgcluster with the associated port
5433.
- Running cmd.exe under Windows as a_user, I can start and stop it
manually in command line using the following commands:

- The cluster data directory is a_pgcluster. Its (filesystem) owner is
a_user with this user having full control on it.

- Now, I would like it to be run automatically at machine startup as a
service.
- So I registered the cluster using the following command:

- When I go to Control Panel - Administrative Tools - Services and
try to start the service, I get the following message:

- I also tried the following commands in command-line:


Can you help me try to figure out what's wrong?
Best regards.
--
OS: WinXP Pro SP3
DBMS: PostgreSQL v.9.1

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Starting a cluster as a service

2012-06-15 Thread Thomas Boussekey

Hello Léa,

Command line, message and commands are missing.
Difficult to help you!

Regards,
Thomas

2012/6/15 Léa Massiot lmhe...@orange.fr

 Hello and thank you for reading my post.

 My problem is that I do not manage to start a PostgreSQL cluster as a
 Windows service.
 The OS is WinXP.

 - I've created a PostgreSQL cluster a_pgcluster with the associated port
 5433.
 - Running cmd.exe under Windows as a_user, I can start and stop it
 manually in command line using the following commands:

 - The cluster data directory is a_pgcluster. Its (filesystem) owner is
 a_user with this user having full control on it.

 - Now, I would like it to be run automatically at machine startup as a
 service.
 - So I registered the cluster using the following command:

 - When I go to Control Panel - Administrative Tools - Services and
 try to start the service, I get the following message:

 - I also tried the following commands in command-line:


 Can you help me try to figure out what's wrong?
 Best regards.
 --
 OS: WinXP Pro SP3
 DBMS: PostgreSQL v.9.1

 --
 View this message in context:
 http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728.html
 Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] Starting PostgreSQL

2008-10-12 Thread Marco Colombo

admin wrote:
 Sorry folks, a perennial one I'm sure ...
 
 I have read the manual and Googled for a couple of hours but still can't
 connect to PostgreSQL 8.3.4 (the PGDG RPMs running on an up to date
 CentOS 5.2).
 
 I continually get this message:
 
 psql: could not connect to server: No such file or firectory
 Is the server running locally and accepting
 connections on Unix domain socket /tmp/.s.PDSQL.0?
 
 Yes, the server is running as 'ps -aux' and 'netstat -l' and 'service
 postgresql status' all confirm.

Do you mean you have something like this in your netstan -l?
unix  2  [ ACC ] STREAM LISTENING 12587  /tmp/.s.PGSQL.5432

note, this is on a linux box with postgresql in standard configuration.
Just look at the port number embedded in the socket name. I don't really
think you can run a process on port 0. I think your psql is looking for
the wrong socket.

Try:
$ psql -p 5432 ...

If you don't see any unix socket for PG (I don't even think that's possible),
then you need to use IP sockets:

$ psql -p 5432 -h localhost ...

 
 service postgresql start/stop/restart works without errors
 pg_ctl start/stop/restart works without errors
 
 There is no socket file in /tmp.

Opps sorry I missed this. Well double check with netstat, but it's
possible your PG is not configured for Unix sockets... even if I
wouldn't know how to do that.

I just checked a CentOS5.2 running PG and there it is:

$ ls -l /tmp/.s.PGSQL.5432
srwxrwxrwx 1 postgres postgres 0 Oct 13 01:22 /tmp/.s.PGSQL.5432

.TM.

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

[GENERAL] Starting PostgreSQL

2008-10-11 Thread admin


Sorry folks, a perennial one I'm sure ...

I have read the manual and Googled for a couple of hours but still can't 
connect to PostgreSQL 8.3.4 (the PGDG RPMs running on an up to date 
CentOS 5.2).


I continually get this message:

psql: could not connect to server: No such file or firectory
Is the server running locally and accepting
connections on Unix domain socket /tmp/.s.PDSQL.0?

Yes, the server is running as 'ps -aux' and 'netstat -l' and 'service 
postgresql status' all confirm.


service postgresql start/stop/restart works without errors
pg_ctl start/stop/restart works without errors

There is no socket file in /tmp.
I believe I have PG configured to listen on port 5432 anyway:

listen_addresses = '*'
port = 5432

Is that enough to make PG listen on a port ... the docs seem to be 
saying that?





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

Re: [GENERAL] Starting PostgreSQL

2008-10-11 Thread Devrim GÜNDÜZ

On Sun, 2008-10-12 at 00:03 +0930, admin wrote:
 psql: could not connect to server: No such file or firectory
 Is the server running locally and accepting
 connections on Unix domain socket /tmp/.s.PDSQL.0?

Socket file name is wrong -- and the port...
-- 
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
   http://www.gunduz.org


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Starting PostgreSQL

2008-10-11 Thread Adrian Klaver

On Saturday 11 October 2008 7:33:20 am admin wrote:
 Sorry folks, a perennial one I'm sure ...

 I have read the manual and Googled for a couple of hours but still can't
 connect to PostgreSQL 8.3.4 (the PGDG RPMs running on an up to date
 CentOS 5.2).

 I continually get this message:

 psql: could not connect to server: No such file or firectory
 Is the server running locally and accepting
 connections on Unix domain socket /tmp/.s.PDSQL.0?

 Yes, the server is running as 'ps -aux' and 'netstat -l' and 'service
 postgresql status' all confirm.

 service postgresql start/stop/restart works without errors
 pg_ctl start/stop/restart works without errors

 There is no socket file in /tmp.
 I believe I have PG configured to listen on port 5432 anyway:

 listen_addresses = '*'
 port = 5432

 Is that enough to make PG listen on a port ... the docs seem to be
 saying that?

What is in the pg_hba.conf file?
Also are you connecting from a remote machine or the local machine?
-- 
Adrian Klaver
[EMAIL PROTECTED]

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

Re: [GENERAL] Starting PostgreSQL

2008-10-11 Thread Tom Lane

admin [EMAIL PROTECTED] writes:
 I continually get this message:

 psql: could not connect to server: No such file or firectory
 Is the server running locally and accepting
 connections on Unix domain socket /tmp/.s.PDSQL.0?

If it's really saying .0, and not .5432, then the problem is on the
client side --- it's got the wrong idea about the port number to
connect to.  Perhaps you have PGPORT set to something bogus in the
client environment?

regards, tom lane

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

Re: [GENERAL] starting a stored procedure+rule AFTER an insert

2007-10-09 Thread Bima Djaloeis

Thanks for the reply, is there any online reference / tutorial for this?

-BD

2007/10/8, Douglas McNaught [EMAIL PROTECTED]:

 Bima Djaloeis [EMAIL PROTECTED] writes:

  I have implemented a stored procedure that writes out the newest DB
  entry on insert, and combined it with a rule.
 
  1) create function newcache() returns void AS 'newCache', 'newCache'
 language
  c;
  2) create rule newcacherule AS on insert to caches do also select
 newcache();
 
  The problem is that newcacherule fires BEFORE the insert has taken
  place, so effectively, I always just get the 2nd newest entry to
  write into my text file while the newest entry is stuck in the
  queue until a new insert come. How can I execute my rule AFTER the
  insert has taken place?

 Rules effectively happen at query parse time.  You probably want an
 AFTER trigger instead.

 -Doug

[GENERAL] starting a stored procedure+rule AFTER an insert

2007-10-08 Thread Bima Djaloeis

Hi, Newbie here,

I have implemented a stored procedure that writes out the newest DB entry on
insert, and combined it with a rule.

1) create function newcache() returns void AS 'newCache', 'newCache'
language c;
2) create rule newcacherule AS on insert to caches do also select
newcache();

The problem is that newcacherule fires BEFORE the insert has taken place, so
effectively, I always just get the 2nd newest entry to write into my text
file while the newest entry is stuck in the queue until a new insert come.
How can I execute my rule AFTER the insert has taken place?

Thanks for reading!

Re: [GENERAL] starting a stored procedure+rule AFTER an insert

2007-10-08 Thread Douglas McNaught

Bima Djaloeis [EMAIL PROTECTED] writes:

 I have implemented a stored procedure that writes out the newest DB
 entry on insert, and combined it with a rule.

 1) create function newcache() returns void AS 'newCache', 'newCache' language
 c;
 2) create rule newcacherule AS on insert to caches do also select newcache();

 The problem is that newcacherule fires BEFORE the insert has taken
 place, so effectively, I always just get the 2nd newest entry to
 write into my text file while the newest entry is stuck in the
 queue until a new insert come. How can I execute my rule AFTER the
 insert has taken place?

Rules effectively happen at query parse time.  You probably want an
AFTER trigger instead.

-Doug

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

[GENERAL] Starting Postgresql

I haven't used the command lines previously having relied on PG Admin.

In the instructions - 
Starting postmaster
Nothing can happen to a database unless the postmaster process is running. As 
the site administrator, there are a number of things you should remember before 
starting the postmaster. These are discussed in the installation and 
configuration sections of this manual. However, if Postgres has been installed 
by following the installation instructions exactly as written, the following 
simple command is all you should need to start the postmaster: 

% postmaster - nowhere is it stated where the command is typed.Perhaps someone 
can tell me?Bob Pawley

Re: [GENERAL] Starting Postgresql

2006-12-20 Thread Ray Stell

a shell

http://en.wikipedia.org/wiki/Shell_%28computing%29


On Wed, Dec 20, 2006 at 10:59:05AM -0800, Bob Pawley wrote:
 I haven't used the command lines previously having relied on PG Admin.
 
 In the instructions - 
 Starting postmaster
 Nothing can happen to a database unless the postmaster process is running. As 
 the site administrator, there are a number of things you should remember 
 before starting the postmaster. These are discussed in the installation and 
 configuration sections of this manual. However, if Postgres has been 
 installed by following the installation instructions exactly as written, the 
 following simple command is all you should need to start the postmaster: 
 
 % postmaster - nowhere is it stated where the command is typed.Perhaps 
 someone can tell me?Bob Pawley
-- 
You have no chance to survive make your time.

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

   http://archives.postgresql.org/

Re: [GENERAL] Starting Postgresql

which in PostgreSQL is

Bob

- Original Message - 
From: Ray Stell [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Wednesday, December 20, 2006 11:07 AM
Subject: Re: [GENERAL] Starting Postgresql

a shell

http://en.wikipedia.org/wiki/Shell_%28computing%29

On Wed, Dec 20, 2006 at 10:59:05AM -0800, Bob Pawley wrote:

I haven't used the command lines previously having relied on PG Admin.

In the instructions -
Starting postmaster
Nothing can happen to a database unless the postmaster process is 
running. As the site administrator, there are a number of things you 
should remember before starting the postmaster. These are discussed in 
the installation and configuration sections of this manual. However, if 
Postgres has been installed by following the installation instructions 
exactly as written, the following simple command is all you should need 
to start the postmaster:

% postmaster - nowhere is it stated where the command is typed.Perhaps 
someone can tell me?Bob Pawley

--
You have no chance to survive make your time.

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

Re: [GENERAL] Starting Postgresql

which in PostgreSQL is

Bob

- Original Message - 
From: Bob Pawley [EMAIL PROTECTED]

To: Ray Stell [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Wednesday, December 20, 2006 11:12 AM
Subject: Re: [GENERAL] Starting Postgresql

which in PostgreSQL is

Bob

- Original Message - 
From: Ray Stell [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Wednesday, December 20, 2006 11:07 AM
Subject: Re: [GENERAL] Starting Postgresql

a shell

http://en.wikipedia.org/wiki/Shell_%28computing%29

On Wed, Dec 20, 2006 at 10:59:05AM -0800, Bob Pawley wrote:

I haven't used the command lines previously having relied on PG Admin.

In the instructions -
Starting postmaster
Nothing can happen to a database unless the postmaster process is 
running. As the site administrator, there are a number of things you 
should remember before starting the postmaster. These are discussed in 
the installation and configuration sections of this manual. However, if 
Postgres has been installed by following the installation instructions 
exactly as written, the following simple command is all you should need 
to start the postmaster:

% postmaster - nowhere is it stated where the command is typed.Perhaps 
someone can tell me?Bob Pawley

--
You have no chance to survive make your time.

---(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: [GENERAL] Starting Postgresql

2006-12-20 Thread Raymond O'Donnell

On 20 Dec 2006 at 11:12, Bob Pawley wrote:

 which in PostgreSQL is

It's not in PostgreSQL - it's the shell of your operating system. In 
Windows, you get that either by clicking Start - Run and typing 
command or cmd (depending on your version of windows), or by 
clicking on Start - Programs - Accessories - Command prompt.


--Ray


--

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
[EMAIL PROTECTED]
--



---(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: [GENERAL] Starting Postgresql

2006-12-20 Thread Richard Huxton


Raymond O'Donnell wrote:

On 20 Dec 2006 at 11:12, Bob Pawley wrote:


which in PostgreSQL is


It's not in PostgreSQL - it's the shell of your operating system. In 
Windows, you get that either by clicking Start - Run and typing 
command or cmd (depending on your version of windows), or by 
clicking on Start - Programs - Accessories - Command prompt.


But in any case, you probably don't want to start it like that. On 
Windows you probably want to go into the service manager (in 
administrative tools iirc) and on Linux something like 
/etc/init.d/postgresql start - that way you'll get the proper startup 
sequence, setting any environment variables and redirecting logging etc.


By the way - what page were you quoting that intruction from? I don't 
recognise it. I'm particularly puzzled because it referred to Postgres 
rather than PostgreSQL.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Starting Postgresql

2006-12-20 Thread Joshua D. Drake

On Wed, 2006-12-20 at 11:13 -0800, Bob Pawley wrote:
 which in PostgreSQL is

Bob, you should be looking into your control-panel and service controls
to start the postgresql service.

Sincerely,

Joshua D. Drake


 
 Bob
 
 - Original Message - 
 From: Bob Pawley [EMAIL PROTECTED]
 To: Ray Stell [EMAIL PROTECTED]
 Cc: Postgresql pgsql-general@postgresql.org
 Sent: Wednesday, December 20, 2006 11:12 AM
 Subject: Re: [GENERAL] Starting Postgresql
 
 
  which in PostgreSQL is
 
  Bob
 
  - Original Message - 
  From: Ray Stell [EMAIL PROTECTED]
  To: Bob Pawley [EMAIL PROTECTED]
  Cc: Postgresql pgsql-general@postgresql.org
  Sent: Wednesday, December 20, 2006 11:07 AM
  Subject: Re: [GENERAL] Starting Postgresql
 
 
 a shell
 
  http://en.wikipedia.org/wiki/Shell_%28computing%29
 
 
  On Wed, Dec 20, 2006 at 10:59:05AM -0800, Bob Pawley wrote:
  I haven't used the command lines previously having relied on PG Admin.
 
  In the instructions -
  Starting postmaster
  Nothing can happen to a database unless the postmaster process is 
  running. As the site administrator, there are a number of things you 
  should remember before starting the postmaster. These are discussed in 
  the installation and configuration sections of this manual. However, if 
  Postgres has been installed by following the installation instructions 
  exactly as written, the following simple command is all you should need 
  to start the postmaster:
 
  % postmaster - nowhere is it stated where the command is typed.Perhaps 
  someone can tell me?Bob Pawley
  -- 
  You have no chance to survive make your time.
 
  
 
 
 ---(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
 
-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




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

   http://archives.postgresql.org/

Re: [GENERAL] Starting Postgresql

Here's the url http://fusion.gat.com/~osborne/dbdoc/postgres/postmaster.htm

Bob

- Original Message - 
From: Richard Huxton dev@archonet.com

To: Raymond O'Donnell [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Wednesday, December 20, 2006 11:43 AM
Subject: Re: [GENERAL] Starting Postgresql

Raymond O'Donnell wrote:

On 20 Dec 2006 at 11:12, Bob Pawley wrote:

which in PostgreSQL is

It's not in PostgreSQL - it's the shell of your operating system. In 
Windows, you get that either by clicking Start - Run and typing 
command or cmd (depending on your version of windows), or by 
clicking on Start - Programs - Accessories - Command prompt.

But in any case, you probably don't want to start it like that. On 
Windows you probably want to go into the service manager (in 
administrative tools iirc) and on Linux something like 
/etc/init.d/postgresql start - that way you'll get the proper startup 
sequence, setting any environment variables and redirecting logging etc.

By the way - what page were you quoting that intruction from? I don't 
recognise it. I'm particularly puzzled because it referred to Postgres 
rather than PostgreSQL.

--
  Richard Huxton
  Archonet Ltd

---(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

Re: [GENERAL] Starting Postgresql

2006-12-20 Thread Uwe C. Schroeder

I think you're better off to use the official documentation.
http://www.postgresql.org/docs/

(chose the docs for the version you're running on the right side of that 
page).

The docs you're currently referencing are for 7.0 - which is stone-age 
postgresql.

On Wednesday 20 December 2006 11:57, Bob Pawley wrote:
 Here's the url http://fusion.gat.com/~osborne/dbdoc/postgres/postmaster.htm

 Bob


 - Original Message -
 From: Richard Huxton dev@archonet.com
 To: Raymond O'Donnell [EMAIL PROTECTED]
 Cc: Postgresql pgsql-general@postgresql.org
 Sent: Wednesday, December 20, 2006 11:43 AM
 Subject: Re: [GENERAL] Starting Postgresql

  Raymond O'Donnell wrote:
  On 20 Dec 2006 at 11:12, Bob Pawley wrote:
  which in PostgreSQL is
 
  It's not in PostgreSQL - it's the shell of your operating system. In
  Windows, you get that either by clicking Start - Run and typing
  command or cmd (depending on your version of windows), or by
  clicking on Start - Programs - Accessories - Command prompt.
 
  But in any case, you probably don't want to start it like that. On
  Windows you probably want to go into the service manager (in
  administrative tools iirc) and on Linux something like
  /etc/init.d/postgresql start - that way you'll get the proper startup
  sequence, setting any environment variables and redirecting logging etc.
 
  By the way - what page were you quoting that intruction from? I don't
  recognise it. I'm particularly puzzled because it referred to Postgres
  rather than PostgreSQL.
 
  --
Richard Huxton
Archonet Ltd
 
  ---(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

-- 
UC

--
Open Source Solutions 4U, LLC   1618 Kelly St
Phone:  +1 707 568 3056 Santa Rosa, CA 95401
Cell:   +1 650 302 2405 United States
Fax:+1 707 568 6416

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

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

Re: [GENERAL] Starting Postgresql

2006-12-20 Thread George Weaver



Original Message From Bob Pawley

Here's the url 
http://fusion.gat.com/~osborne/dbdoc/postgres/postmaster.htm


Bob,

The above documentation is circa version 7.0.

It might be easier to use the current PostgreSQL official documentation. 
See for example:


http://www.postgresql.org/docs/8.2/static/server-start.html

Regards,
George 




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

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

Re: [GENERAL] Starting Postgresql

2006-12-20 Thread Richard Huxton


Bob Pawley wrote:

Here's the url http://fusion.gat.com/~osborne/dbdoc/postgres/postmaster.htm


As the others say, use the official docs. And perhaps drop osborne a 
note to let him know his docs are out of date.


--
  Richard Huxton
  Archonet Ltd

---(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

[GENERAL] starting postgres on windows

2006-11-27 Thread garry saddington

How would I start Postgres on windows as an un-privileged user without
logging into an un-privileged account. I have tried the -U switch but it
still complains. I have version 8.
kind regards
Garry


---(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: [GENERAL] starting postgres on windows

2006-11-27 Thread Harald Armin Massa


Garry,

the standard recommendation is to install PostgreSQL as a service on
windows; logging in with an own low privilege user account, usually named
postgres.

That usage of a service is recommended because it solves all the usual
problems of services :) (start, shut down, login as seperate user )

However: I guess you have an privileged user (=Member of Administrators),
and want to start PostgreSQL as an unprivileged user. So just create a
command shell as that user:

runas /user:postgres cmd

and start and stop PostgreSQL within that shell. (in the long run, you can
change that line to immediately start PostgreSQL instead of going via cmd)

For 8.2 there is scheduled a drop privilege method of startup on windows,
so that starting PostgreSQL as Administrator is no longer a problem, since
PostgreSQL will drop it privileges immediately after start.


hth

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.

Re: [GENERAL] starting postgres on windows

2006-11-27 Thread Raymond O'Donnell

On 27 Nov 2006 at 8:03, garry saddington wrote:

 How would I start Postgres on windows as an un-privileged user without
 logging into an un-privileged account. I have tried the -U switch but
 it still complains. I have version 8. 

If you installed PostgreSQL using the installer - which I'd 
recommend, as it makes life *very* easy - then along the way it 
should have created an unprivileged user. Have a look at the server 
service in Administrative Tools - Services, and on the Log on tab 
you should see the user under which it's running.

--Ray.


--

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
[EMAIL PROTECTED]
--



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

[GENERAL] Starting Postgresql as windows service

2006-04-25 Thread Rajarajan

Hi I want to start psql as a windows service manually.How to do that?i was able to register the service but able to start it..when i start it ..i got the following message..---Services
---The PostgreSQL service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
---OK ---thanks in Advance..-- My only Superstition is belief in facts

Re: [GENERAL] Starting Postgresql as windows service

2006-04-25 Thread Harald Armin Massa

Rajarajan,please check the postgresql logs witin your data directory pg_logyour data directory defaults to [programs]\Postgresql\8.1\datawhere [programs] is ~Programs and Files in US Windows, and Programme in German Windows.
Propably there is some problem with postgresql.conf or access to your datafiles; the log may tellhthHaraldOn 4/25/06, Rajarajan
 [EMAIL PROTECTED] wrote:
Hi I want to start psql as a windows service manually.How to do that?i was able to register the service but able to start it..when i start it ..i got the following message..---Services
---The PostgreSQL service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

---OK ---thanks in Advance..-- 
My only Superstition is belief in facts

-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-PostgreSQL - supported by a community that does not put you on hold

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Sun, 2005-10-30 at 23:08 -0500, Tom Lane wrote:
 Vlad [EMAIL PROTECTED] writes:
  I'm looking for some help in regards to letting Posresql use more
  memory.
 
 8.0 can't go past 2Gb of shared memory, and there is really no reason
 to try because its performance will get worse not better with more than
 about 5 shared buffers.

Unless you turn off the bgwriter, in which case going higher can still
have benefit given the right circumstances.

 8.1 will relax the 2Gb limit, but it's still far from clear that there's
 any point in it.  The conventional wisdom is that you should leave most
 of memory free for kernel disk cache, not try to eat it all in shared
 buffers.  I haven't seen any evidence that that's changed in 8.1.  It
 might possibly make sense to use several Gb of shared buffers in a
 machine with 16Gb or more of RAM, but not in one with only 4Gb RAM.

I'm not sure we have any good tests of that either way, do we? I'm not
certain why we would trust OS cache any more than we could trust the
shared buffers. But setting it too high would probably overuse backend
memory for most variable query workloads.

 BTW, where did you get the idea that it was sensible to set work_mem
 higher than maintenance_work_mem?  That's just nuts.

Surely if you choose to favour query sort performance say over vacuum
performance that is a reasonable design choice in some specific
circumstances? Not the general case, agreed.

There are no assumptions in the code that work_mem is always smaller.
Tasks are assigned to use maintenance_work_mem when they are considered
to be maintenance tasks.

Best Regards, Simon Riggs



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

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, Oct 31, 2005 at 12:16:59PM +, Simon Riggs wrote:
  8.0 can't go past 2Gb of shared memory, and there is really no reason
  to try because its performance will get worse not better with more than
  about 5 shared buffers.
 
 Unless you turn off the bgwriter, in which case going higher can still
 have benefit given the right circumstances.

Is there any particular reason to turn that off? You want dirty pages
written out. Doing them asyncronously beforehand means you don't have
to wait for it at commit time. It also allows the OS to schedule the
blocks into a better write order.

Anyway, the original writer didn't specify an architechure. If it is a
32bit one it is entirly possible that the memory map simply has no
large contiguous space to map the shared memory.

  8.1 will relax the 2Gb limit, but it's still far from clear that there's
  any point in it.  The conventional wisdom is that you should leave most
  of memory free for kernel disk cache, not try to eat it all in shared
  buffers.  I haven't seen any evidence that that's changed in 8.1.  It
  might possibly make sense to use several Gb of shared buffers in a
  machine with 16Gb or more of RAM, but not in one with only 4Gb RAM.
 
 I'm not sure we have any good tests of that either way, do we? I'm not
 certain why we would trust OS cache any more than we could trust the
 shared buffers. But setting it too high would probably overuse backend
 memory for most variable query workloads.

Well, it comes down to a thought experiment. Any disk blocks you have in
the shared buffers will also be in the system cache. If you give 4GB to
shared buffers, then there will be 4GB of data in the system cache which
is not directly useful. So it seems shared buffers should be large
enough to hold all the info PostgreSQL needs at any particular moment,
anything else is just wasteful. Getting data out of the system cache is
not terribly expensive, I timed it at 50 microseconds per page on my
oldish laptop.

Secondly, you're assuming that PostgreSQLs caching is at least as
efficient as the OS caching, which is more of an assertion than
anything else.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgp6Bb963f7JX.pgp
Description: PGP signature

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, 2005-10-31 at 14:14 +0100, Martijn van Oosterhout wrote:
 On Mon, Oct 31, 2005 at 12:16:59PM +, Simon Riggs wrote:
   8.0 can't go past 2Gb of shared memory, and there is really no reason
   to try because its performance will get worse not better with more than
   about 5 shared buffers.
  
  Unless you turn off the bgwriter, in which case going higher can still
  have benefit given the right circumstances.
 
 Is there any particular reason to turn that off? 

Well yeh. If things work faster without it, then off it goes - or at
least parameter settings vastly altered.

 You want dirty pages
 written out. Doing them asyncronously beforehand means you don't have
 to wait for it at commit time. It also allows the OS to schedule the
 blocks into a better write order.

Only assuming you have a constant heavy write workload.

   8.1 will relax the 2Gb limit, but it's still far from clear that there's
   any point in it.  The conventional wisdom is that you should leave most
   of memory free for kernel disk cache, not try to eat it all in shared
   buffers.  I haven't seen any evidence that that's changed in 8.1.  It
   might possibly make sense to use several Gb of shared buffers in a
   machine with 16Gb or more of RAM, but not in one with only 4Gb RAM.
  
  I'm not sure we have any good tests of that either way, do we? I'm not
  certain why we would trust OS cache any more than we could trust the
  shared buffers. But setting it too high would probably overuse backend
  memory for most variable query workloads.
 
 Well, it comes down to a thought experiment. Any disk blocks you have in
 the shared buffers will also be in the system cache. 

Each have different and independent cache replacement... 

 If you give 4GB to
 shared buffers, then there will be 4GB of data in the system cache which
 is not directly useful. So it seems shared buffers should be large
 enough to hold all the info PostgreSQL needs at any particular moment,
 anything else is just wasteful. Getting data out of the system cache is
 not terribly expensive, I timed it at 50 microseconds per page on my
 oldish laptop.
 
 Secondly, you're assuming that PostgreSQLs caching is at least as
 efficient as the OS caching, which is more of an assertion than
 anything else.

Do you doubt that? Why would shared_buffers be variable otherwise? 

Best Regards, Simon Riggs


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

   http://archives.postgresql.org

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

2005-10-31 Thread Vlad

 Anyway, the original writer didn't specify an architechure. If it is a
 32bit one it is entirly possible that the memory map simply has no
 large contiguous space to map the shared memory.

it's 32bit. The actual problem of giving more buffers to postgresql
was solved with the help of the following post:
http://docs.freebsd.org/cgi/getmsg.cgi?fetch=83003+0+archive/2002/freebsd-hackers/20020804.freebsd-hackers

It looks like despite to the comment in /usr/src/sys/i386/include/vmparam.h

#ifndef MAXDSIZ
#define MAXDSIZ (512UL*1024*1024)   /* max data size */
#endif

for FreeBSD MAXDSIZ actually tells kernel where to start allocating
memory, but not the maximum allowable size. Cause as soon as I lowered
this value from  2500UL*1024*1024(what I set when I was setting up the
server) to 1024UL*1024*1025, I was able to further increase shared
buffers in postgres.conf.

Also, while I can agree with the point that maybe OS file caching
algorythm is more efficient than PostgreSQL's, but that still doest
give us single meaning answer because:
1) for PostgreSQL the job of fetching the data from OS buffers should
imply some overhead compared to accessing the data cached in shared
buffers.
2) there is no guarantee that OS dedicates all the rest of available
RAM for file caching. In fact, in case there are other processes
running on the server, perhaps I want to make sure that that much
memory is dedicated solely  for PostgreSQL data caching, and the only
way for that is increasing shared buffers.

later today I will do some performance testing with shared buffers set
to 50k as Tom suggested and then with, lets say 200k and post the
results here.

--
Vlad

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

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

Simon Riggs [EMAIL PROTECTED] writes:
 On Mon, 2005-10-31 at 14:14 +0100, Martijn van Oosterhout wrote:
 On Mon, Oct 31, 2005 at 12:16:59PM +, Simon Riggs wrote:
 I'm not sure we have any good tests of that either way, do we? I'm not
 certain why we would trust OS cache any more than we could trust the
 shared buffers. But setting it too high would probably overuse backend
 memory for most variable query workloads.
 
 Well, it comes down to a thought experiment. Any disk blocks you have in
 the shared buffers will also be in the system cache. 

 Each have different and independent cache replacement... 

The real point is that RAM dedicated to shared buffers can't be used for
anything else [1], whereas letting the kernel manage it gives you some
flexibility (for instance, to deal with transient large memory demands
by individual backends, or from stuff unrelated to Postgres).  A system
configured to give most of RAM to shared buffers might look good on
sufficiently narrow test cases, but its performance will be horribly
brittle: it will go into swap thrashing on any small provocation.  The
extra 50usec or whatever to get stuff from a kernel disk buffer instead
of our own shared buffer is a good tradeoff to get flexibility in the
amount of stuff actually buffered at any one instant.

[1] unless you are on a platform where the kernel doesn't think SysV
shared memory should be locked in RAM.  In that case, what you have is a
large arena that is subject to being swapped out ... and a disk buffer
that's been swapped to disk is demonstrably worse than no buffer at all.
(Hint: count the I/Os involved, especially when the page is dirty.)

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, Oct 31, 2005 at 01:34:12PM +, Simon Riggs wrote:
  Secondly, you're assuming that PostgreSQLs caching is at least as
  efficient as the OS caching, which is more of an assertion than
  anything else.
 
 Do you doubt that? Why would shared_buffers be variable otherwise? 

Because the optimal hasn't been found and is probably different for
each machine.

There have been tests that demonstrate that you can raise the buffers
to a certain point which is optimal and after that it just doesn't
help [1]. They peg optimal size at 5-10% of memory.

Also, as Tom pointed out, any memory assigned to shared buffers can't
be used for sorts, temporary tables, plain old disk caching, trigger
queues or anything else that isn't shared between backends. There are
far more useful uses of memory than just buffering disk blocks.

Have a nice day,

[1] http://archives.postgresql.org/pgsql-performance/2004-10/msg00110.php
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpjWkVPkgT15.pgp
Description: PGP signature

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

Martijn van Oosterhout kleptog@svana.org writes:
 There have been tests that demonstrate that you can raise the buffers
 to a certain point which is optimal and after that it just doesn't
 help [1]. They peg optimal size at 5-10% of memory.
 [1] http://archives.postgresql.org/pgsql-performance/2004-10/msg00110.php

Note however that it's reasonable to think that 8.1 may do better than
8.0 did at performing well with large values of shared_buffers,
primarily because we got rid of the StrategyDirtyBufferList overhead:
http://archives.postgresql.org/pgsql-performance/2004-10/msg00112.php

It'd be interesting to repeat the above-mentioned tests with 8.1.

regards, tom lane

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, Oct 31, 2005 at 09:54:39AM -0500, Tom Lane wrote:
 Note however that it's reasonable to think that 8.1 may do better than
 8.0 did at performing well with large values of shared_buffers,
 primarily because we got rid of the StrategyDirtyBufferList overhead:
 http://archives.postgresql.org/pgsql-performance/2004-10/msg00112.php
 
 It'd be interesting to repeat the above-mentioned tests with 8.1.

Well, OSDL has run tests on PostgreSQL as recently as 20050908 but the
host with the results isn't responding to me, so no idea what the tests
were. Also, they use various tests involving PostgreSQL to test the
scalability of the Linux kernel, so you can see how postgres runs with
various different kernel patches.

http://www.osdl.org/lab_activities/kernel_testing/stp/search.lnk/search_test_requests

Enter postgresql in the software field, or select one of the pgsql
tests. Maybe someone else will have more luck than me getting the
results...

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpDjgw4StWex.pgp
Description: PGP signature

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, 2005-10-31 at 09:35 -0500, Tom Lane wrote:
 Simon Riggs [EMAIL PROTECTED] writes:
  On Mon, 2005-10-31 at 14:14 +0100, Martijn van Oosterhout wrote:
  On Mon, Oct 31, 2005 at 12:16:59PM +, Simon Riggs wrote:
  I'm not sure we have any good tests of that either way, do we? I'm not
  certain why we would trust OS cache any more than we could trust the
  shared buffers. But setting it too high would probably overuse backend
  memory for most variable query workloads.
  
  Well, it comes down to a thought experiment. Any disk blocks you have in
  the shared buffers will also be in the system cache. 
 
  Each have different and independent cache replacement... 
 
 The real point is that RAM dedicated to shared buffers can't be used for
 anything else [1], whereas letting the kernel manage it gives you some
 flexibility (for instance, to deal with transient large memory demands
 by individual backends, or from stuff unrelated to Postgres).  A system
 configured to give most of RAM to shared buffers might look good on
 sufficiently narrow test cases, but its performance will be horribly
 brittle: it will go into swap thrashing on any small provocation.  The
 extra 50usec or whatever to get stuff from a kernel disk buffer instead
 of our own shared buffer is a good tradeoff to get flexibility in the
 amount of stuff actually buffered at any one instant.

Agreed. But that is an argument in favour of more easily controllable
server memory management, not a definitive argument against setting
shared_ buffers higher.

 [1] unless you are on a platform where the kernel doesn't think SysV
 shared memory should be locked in RAM.  In that case, what you have is a
 large arena that is subject to being swapped out ... and a disk buffer
 that's been swapped to disk is demonstrably worse than no buffer at all.
 (Hint: count the I/Os involved, especially when the page is dirty.)

This is a disaster for any database, not just PostgreSQL. But most other
DBMS do something about this, for example on Linux, Solaris, HP/UX, AIX
a certain orange DBMS provides additional support for making shared
memory non-swappable. 

Have other people used lock_sga = true in Oracle? Or do we think this is
a benchmark gimmic that should never be used in production?

We would need to issue a shmctl() with SHM_LOCK, which requires enabling
the CAP_IPC_LOCK capability. 

Best Regards, Simon Riggs


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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

Simon Riggs [EMAIL PROTECTED] writes:
 On Mon, 2005-10-31 at 09:35 -0500, Tom Lane wrote:
 The real point is that RAM dedicated to shared buffers can't be used for
 anything else [1], whereas letting the kernel manage it gives you some
 flexibility (for instance, to deal with transient large memory demands
 by individual backends, or from stuff unrelated to Postgres).

 Agreed. But that is an argument in favour of more easily controllable
 server memory management, not a definitive argument against setting
 shared_ buffers higher.

Well, as long as shared_buffers is a fixed parameter, it's an argument
against setting shared_buffers higher ;-).  But the larger point here
is that Postgres does not have the knowledge needed to make the same
kinds of memory tradeoffs that the kernel does.  I think trying to usurp
this kernel functionality would be exactly the wrong design direction
for us to take.

 [1] unless you are on a platform where the kernel doesn't think SysV
 shared memory should be locked in RAM.

 This is a disaster for any database, not just PostgreSQL. But most other
 DBMS do something about this, for example on Linux, Solaris, HP/UX, AIX
 a certain orange DBMS provides additional support for making shared
 memory non-swappable. 

Yeah, and we should do that too on platforms where it can be done
reasonably (ie, without root privs).

regards, tom lane

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

2005-10-31 Thread Scott Marlowe

On Mon, 2005-10-31 at 10:58, Simon Riggs wrote:
 On Mon, 2005-10-31 at 15:44 +0100, Martijn van Oosterhout wrote:
  On Mon, Oct 31, 2005 at 01:34:12PM +, Simon Riggs wrote:
Secondly, you're assuming that PostgreSQLs caching is at least as
efficient as the OS caching, which is more of an assertion than
anything else.
   
   Do you doubt that? Why would shared_buffers be variable otherwise? 
  
  Because the optimal hasn't been found and is probably different for
  each machine.
  
  There have been tests that demonstrate that you can raise the buffers
  to a certain point which is optimal and after that it just doesn't
  help [1]. They peg optimal size at 5-10% of memory.
 
 Please read the rest of that thread. Those results and their conclusions
 were refuted in some detail, which lead to a number of optimizations in
 8.0 and 8.1, mostly written by Tom.
 
  Also, as Tom pointed out, any memory assigned to shared buffers can't
  be used for sorts, temporary tables, plain old disk caching, trigger
  queues or anything else that isn't shared between backends. There are
  far more useful uses of memory than just buffering disk blocks.
 
 Your point was about cache efficiency as an argument for not increasing
 shared_buffers. Politely, I don't accept that argument. Clearly, there
 are some other considerations (for which I agree completely) but those
 don't prevent you increasing shared_buffers, they just place limits on
 your overall memory budget which could effect shared_buffers of course.

As I understand it, when the last backend referencing a collection of
data stops referencing it, that the buffers holding that data are
released, and if, a second later, another backend wants the data, then
it has to go to the Kernel for it again.

Is this still the case in 8.1?

---(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: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, 2005-10-31 at 14:50 -0600, Scott Marlowe wrote:

 As I understand it, when the last backend referencing a collection of
 data stops referencing it, that the buffers holding that data are
 released, and if, a second later, another backend wants the data, then
 it has to go to the Kernel for it again.

Unreferenced data is not immediately released to the kernel. When a
backend requests a datablock that is not in shared_buffers it will
select an unreferenced buffer, write it if required (hopefully not
required because of the bgwriter), then overwrite the shared_buffer
cache with the datablock it is trying to read from disk. All reads and
writes go through the OS cache, which does pretty much the same thing
but with a different algorithm. So disk might just mean OS cache.

There's zero *requirement* for the OS cache to be bigger than
shared_buffers. Martijn and Tom discuss that there are a number of
advantages to not overallocating shared_buffers, which is the reason why
the usual recommendation is to not do that.

Best Regards, Simon Riggs


---(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: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, Oct 31, 2005 at 02:50:31PM -0600, Scott Marlowe wrote:
  Your point was about cache efficiency as an argument for not increasing
  shared_buffers. Politely, I don't accept that argument. Clearly, there
  are some other considerations (for which I agree completely) but those
  don't prevent you increasing shared_buffers, they just place limits on
  your overall memory budget which could effect shared_buffers of course.
 
 As I understand it, when the last backend referencing a collection of
 data stops referencing it, that the buffers holding that data are
 released, and if, a second later, another backend wants the data, then
 it has to go to the Kernel for it again.
 
 Is this still the case in 8.1?

Depends what you mean. What one backend uses stays in the shared
buffers when it's done. It's only removed to make room for other blocks
that have been requested. Whether it's still there after a second kind
of depends on how much other data you read in the meantime and whether
the caching algorithm decided the data was old enough that you wern't
likely to need it soon.

It's kind of like the kernel cache, once you've been running for a
while it's always full of blocks of data. There's no point forgetting
perfectly good data. The only time you don't need to throw away blocks
is if your database is smaller than your memory,

You mentioned something about those OSDL tests, where can we download
the results? I just get told khack.osdl.org is unreachable...
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpNQM9ZmK6Rs.pgp
Description: PGP signature

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

Scott Marlowe [EMAIL PROTECTED] writes:
 I was mainly wondering if that behaviour had changed, if, when the data
 are released, they are still held in shared memory until forced out by
 newer / more popular data.  Which would make the buffer pool a real
 cache.

Huh?  It's always done that.

regards, tom lane

---(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: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

2005-10-31 Thread Scott Marlowe

On Mon, 2005-10-31 at 15:44, Simon Riggs wrote:
 On Mon, 2005-10-31 at 14:50 -0600, Scott Marlowe wrote:
 
  As I understand it, when the last backend referencing a collection of
  data stops referencing it, that the buffers holding that data are
  released, and if, a second later, another backend wants the data, then
  it has to go to the Kernel for it again.
 
 Unreferenced data is not immediately released to the kernel. When a
 backend requests a datablock that is not in shared_buffers it will
 select an unreferenced buffer, write it if required (hopefully not
 required because of the bgwriter), then overwrite the shared_buffer
 cache with the datablock it is trying to read from disk. All reads and
 writes go through the OS cache, which does pretty much the same thing
 but with a different algorithm. So disk might just mean OS cache.

Hence the reason I carefully hedged my reference as getting it from the
kernel.  I wasn't really wanting to discuss how the kernel manages to
make it magically appear, as it's the kernel's job to do it and keep
track of it.

The point behind my post was that the kernel caches AND buffers, while
postgresql technically only really seems to buffer, with a little
incidental caching thrown in if you catch it at the right time.

I was mainly wondering if that behaviour had changed, if, when the data
are released, they are still held in shared memory until forced out by
newer / more popular data.  Which would make the buffer pool a real
cache.

As long as postgresql releases hold on all those buffers when they're
not needed, I would think it was a buffer, not a real cache, and it
shouldn't normally be tuned as a cache.

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

2005-10-31 Thread Scott Marlowe

On Mon, 2005-10-31 at 16:12, Tom Lane wrote:
 Scott Marlowe [EMAIL PROTECTED] writes:
  I was mainly wondering if that behaviour had changed, if, when the data
  are released, they are still held in shared memory until forced out by
  newer / more popular data.  Which would make the buffer pool a real
  cache.

Oh, sorry.  I Was under the impression that once it wasn't needed the
buffers just dropped the data completely.  Thanks

---(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: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD

On Mon, 2005-10-31 at 14:48 -0800, Chris Travers wrote:
 Simon Riggs wrote:

 Your point was about cache efficiency as an argument for not increasing
 shared_buffers. Politely, I don't accept that argument. Clearly, there
 are some other considerations (for which I agree completely) but those
 don't prevent you increasing shared_buffers, they just place limits on
 your overall memory budget which could effect shared_buffers of course.
 

 I can see some circumstances where it might make some sense to have high 
 shared buffer arrangements.
 
 However, I think that Tom and others are speaking to typical cases, and 
 I think you seem to be speaking to the case where you have a database 
 where you have many reads and only a few writes, and where a few tables 
 are far more often used that the rest.  So it strikes me as an argument 
 against making such the general recommendation.  Of course, if your 
 database benefits from turning off bgwriter and increasing shared 
 buffers, you might find that useful.  Just be aware that it is likely to 
 be applicable only to a small subset of the PostgreSQL deployments.

This all depends upon what you see as typical. I see more than one
typical deployment - I see three, maybe more:

- OLTP/ Current State data management
- Data Warehouse
- Log Archiver

Each are fairly different in many respects, so I see few general
recommendations that really do apply to everybody. So thats why I
didn't attempt to make a general recommendation myself, just pointing
out that you can if you want and there's nothing physically stopping you
from putting shared_buffers high (in 8.1).

Best Regards, Simon Riggs



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

[GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD 6.0]

2005-10-30 Thread Vlad

Hi,

I'm looking for some help in regards to letting Posresql use more
memory. It fails to start with this message:

shmat(id=65536) failed: Cannot allocate shared bufers

Max buffers I can start it with is 115200. Server has 4gig of RAM,
I've adjuted MAXDSIZ to 2.5Gigs. Here is other kernel settings

kern.ipc.shmall: 700
kern.ipc.shmseg: 8192
kern.ipc.shmmni: 8291
kern.ipc.shmmax: 20
kern.ipc.semaem: 10
kern.ipc.semvmx: 32767
kern.ipc.semusz: 332
kern.ipc.semume: 384
kern.ipc.semopm: 300
kern.ipc.semmsl: 300
kern.ipc.semmnu: 384
kern.ipc.semmns: 384
kern.ipc.semmni: 384
kern.ipc.semmap: 384

postgresql.conf:

shared_buffers = 152000 # min 16, at least max_connections*2, 8KB each
work_mem = 5# min 64, size in KB
maintenance_work_mem = 4# min 1024, size in KB
max_stack_depth = 6048  # min 100, size in KB
max_fsm_pages = 200 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 28192   # min 100, ~50 bytes each

and there is no limits on pgsql user.

any help / ideas will be appreciated

--
Vlad

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD 6.0]

2005-10-30 Thread Tom Lane

Vlad [EMAIL PROTECTED] writes:
 I'm looking for some help in regards to letting Posresql use more
 memory.

8.0 can't go past 2Gb of shared memory, and there is really no reason
to try because its performance will get worse not better with more than
about 5 shared buffers.

8.1 will relax the 2Gb limit, but it's still far from clear that there's
any point in it.  The conventional wisdom is that you should leave most
of memory free for kernel disk cache, not try to eat it all in shared
buffers.  I haven't seen any evidence that that's changed in 8.1.  It
might possibly make sense to use several Gb of shared buffers in a
machine with 16Gb or more of RAM, but not in one with only 4Gb RAM.

BTW, where did you get the idea that it was sensible to set work_mem
higher than maintenance_work_mem?  That's just nuts.

See the pgsql-performance archives for past discussions of this topic.

regards, tom lane

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

Re: [GENERAL] Starting PostgreSQL 8.0.4 with more memory [FreeBSD 6.0]

2005-10-30 Thread Vlad

Tom,

I understood your point on memory usage. Out of curiosity - 115200
buffers seems to be little less than 1 gig (I assume 1 buffer = 8k),
so I could not get any closer to 2gigs anyways

Is it practical experience that more than 5 buggers actually hurts
postgresql performance? Any ideas why? What about really big
databases?

 BTW, where did you get the idea that it was sensible to set work_mem
 higher than maintenance_work_mem?  That's just nuts.

I was just playing with different settings to see if there is one I
can adjust to get it started.


 See the pgsql-performance archives for past discussions of this topic.

ok, thnx

--

Vlad

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

[GENERAL] Starting PostgreSQL on WinXP is not working

2005-06-09 Thread Rodrigo Katsumoto Sakai

  Hi, i'm working with PostgreSQL for a long time (about three years), but 
always on Linux box. But recently, I had to intall PostgreSQL on a WinXP 
machine!
  The installation works fine, although the starting service did not works in 
the finalization of the installation! The installation was done in a WinXP SP1 
as a service!
  My problem is that I installed with admin rights, but created the user 
postgres for the system and user postgres for the database. The user postgres 
for the system is supposed to be used to start the service, but i think it is 
not happening, the system tries to start the service with the user that login 
in the system (the admin). If I trie to start the service with a user without 
admin privileges it gives me this error:

2005-06-08 15:14:07 NOTICE:  Unknown win32 socket error code: 10106
2005-06-08 15:14:07 LOG:  could not create IPv4 socket: Invalid argument
2005-06-08 15:14:07 WARNING:  could not create listen socket for localhost
2005-06-08 15:14:07 FATAL:  no socket created for listening

  I googled a lot and dind't find anything with this kind of error. So, I 
really need some help!
  Thanks a lot!!

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

Re: [GENERAL] Starting PostgreSQL on WinXP is not working

2005-06-09 Thread Magnus Hagander

   Hi, i'm working with PostgreSQL for a long time (about 
 three years), but always on Linux box. But recently, I had to 
 intall PostgreSQL on a WinXP machine!
   The installation works fine, although the starting service 
 did not works in the finalization of the installation! The 
 installation was done in a WinXP SP1 as a service!
   My problem is that I installed with admin rights, but 
 created the user postgres for the system and user postgres 
 for the database. The user postgres for the system is 
 supposed to be used to start the service, but i think it is 
 not happening, the system tries to start the service with the 
 user that login in the system (the admin). If I trie to start 
 the service with a user without admin privileges it gives me 
 this error:
 
 2005-06-08 15:14:07 NOTICE:  Unknown win32 socket error code: 10106
 2005-06-08 15:14:07 LOG:  could not create IPv4 socket: 
 Invalid argument
 2005-06-08 15:14:07 WARNING:  could not create listen socket 
 for localhost
 2005-06-08 15:14:07 FATAL:  no socket created for listening
 
   I googled a lot and dind't find anything with this kind of 
 error. So, I really need some help!
   Thanks a lot!!

10106 is The requested service provider could not be loaded or
initialized..

it looks like your TCP stack is broken somehow. I'd look into firewall
and antivirus software. Either it has some blocking functions that
returns weird results, or it's just broken  in general.

//Magnus

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

[GENERAL] starting postgresql with pgsql password - workarounds?

2005-05-20 Thread Duane Winner

hello,
I've been using postgresql for about a year now, and am pretty 
comfortable with the basics, bu there has been something bugging me for 
a while now:

I set the METHOD in pg_hba.conf to md5 so that a password is required 
from all users, from all hosts.

The only problem is that if the server restarts, postgresql will not 
start until somebody goes to the console and enters the password for the 
pgsql account.

Is there a solution for this solution?
Thanks,
DW
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] starting postgresql with pgsql password - workarounds?

2005-05-20 Thread Franco Bruno Borghesi

This is not a PostgreSQL problem, it's the script you are using for
startup that has some problem. The pg_hba method is for connection
stablishment. PostgreSQL will start no matter what you put there.

Startup scripts are usually run as root, and postgresql script should su
to the postgresql user to start the database. I don't know what your
script is doing, but root should be able to su to any user without
password.

Check your script, post it if you want. It would be usefull to know what system you are using also (linux/bsd/solaris/etc).
2005/5/20, Duane Winner [EMAIL PROTECTED]:
hello,I've been using postgresql for about a year now, and am prettycomfortable with the basics, bu there has been something bugging me fora while now:I set the METHOD in pg_hba.conf to md5 so that a password is required
from all users, from all hosts.The only problem is that if the server restarts, postgresql will notstart until somebody goes to the console and enters the password for thepgsql account.Is there a solution for this solution?
Thanks,DW---(end of broadcast)---TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] starting postgresql with pgsql password - workarounds?

2005-05-20 Thread Duane Winner

I am using the default startup script that is supplied with the FreeBSD 
port (/usr/local/etc/rc.d/010.pgsql.sh) and enabling it in /etc/rc.d 
with -o -i flags so listens on TCP/IP

Also, I should mention that the password I mentioned is NOT the password 
for the local (Unix) pgsql account, but the password I set for the 
postgresql database superuser, pgsql. That is the password I need to 
enter to get postgresql to start.

Thanks,
DW
-
#!/bin/sh
# $FreeBSD: ports/databases/postgresql74-server/files/pgsql.sh.tmpl,v 
1.17 2005/03/19 03:51:45 girgen Exp $
#
# PROVIDE: postgresql
# REQUIRE: LOGIN
# KEYWORD: FreeBSD shutdown
#
# Add the following line to /etc/rc.conf to enable PostgreSQL:
#
#  postgresql_enable=YES
#  # optional
#  postgresql_data=/usr/local/pgsql/data
#  postgresql_flags=-w -s -m fast
#
# This scripts takes one of the following commands:
#
#   start stop restart reload status initdb
#
# For postmaster startup options, edit ${postgresql_data}/postgresql.conf

prefix=/usr/local
. /etc/rc.subr
load_rc_config postgresql
# set defaults
postgresql_enable=${postgresql_enable:-NO}
postgresql_flags=${postgresql_flags:--w -s -m fast}
postgresql_user=pgsql
eval postgresql_data=${postgresql_data:-~${postgresql_user}/data}
postgresql_class=${postgresql_class:-default}
name=postgresql
rcvar=`set_rcvar`
command=${prefix}/bin/pg_ctl
command_args=-D ${postgresql_data} ${postgresql_flags} $1
extra_commands=reload initdb
start_cmd=postgresql_command start
stop_cmd=postgresql_command stop
restart_cmd=postgresql_command restart
reload_cmd=postgresql_command reload
status_cmd=postgresql_command status
initdb_cmd=postgresql_initdb
postgresql_command()
{
   su -l ${postgresql_user} -c exec ${command} ${command_args}
}
  
postgresql_initdb()
{
   su -l -c ${postgresql_class} ${postgresql_user} -c exec 
${prefix}/bin/initdb -D ${postgresql_data}
}

run_rc_command $1
-
Franco Bruno Borghesi wrote:
This is not a PostgreSQL problem, it's the script you are using for 
startup that has some problem. The pg_hba method is for connection 
stablishment. PostgreSQL will start no matter what you put there.

Startup scripts are usually run as root, and postgresql script should 
su to the postgresql user to start the database. I don't know what 
your script is doing, but root should be able to su to any user 
without password.

Check your script, post it if you want. It would be usefull to know 
what system you are using also (linux/bsd/solaris/etc).

2005/5/20, Duane Winner [EMAIL PROTECTED] 
mailto:[EMAIL PROTECTED]:

hello,
I've been using postgresql for about a year now, and am pretty
comfortable with the basics, bu there has been something bugging
me for
a while now:
I set the METHOD in pg_hba.conf to md5 so that a password is required
from all users, from all hosts.
The only problem is that if the server restarts, postgresql will not
start until somebody goes to the console and enters the password
for the
pgsql account.
Is there a solution for this solution?
Thanks,
DW
---(end of
broadcast)---
TIP 6: Have you searched our list archives?
   http://archives.postgresql.org
http://archives.postgresql.org


---(end of broadcast)---
TIP 3: 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: [GENERAL] starting postgresql with pgsql password - workarounds?

2005-05-20 Thread Franco Bruno Borghesi

mmmhhh, I have never installed postgresql from the ports. I don´t know
what the script is doing, probably it´s checking that Postgresql
directory is initialized. 

Anyway, here is my homemade script, you could replace yours with it (check it first, but it´s quite simple).
My script does not tell postgresql to listen on tcp sockets, but you can enable it in your postgresql.conf.

#!/bin/sh

case $1 in
start)
 echo -n  postgresql
 su -l pgsql -c '~/bin/pg_ctl start'
 ;;
stop)
 echo -n  postgresql
 su -l pgsql -c '~/bin/pg_ctl stop -mf'
 ;;
restart)
 echo -n reloading postgresql
 echo  stopping
 $0 stop
 echo 
 echo  starting
 $0 start
 echo 
 ;;
reload)
 echo  reloading postgreSQL
 su -l pgsql -c '~/bin/pg_ctl reload -mf'
 echo 
 ;;
status)
 echo -n  postgresql
 su -l pgsql -c '~/bin/pg_ctl status'
 ;;
*)
 echo 
 echo Use: $0 [ start | stop | restart | reload | status ]
 echo 
 ;;
esac


exit 0

Hope it helps.
2005/5/20, Duane Winner [EMAIL PROTECTED]:
I am using the default startup script that is supplied with the FreeBSDport (/usr/local/etc/rc.d/010.pgsql.sh) and enabling it in /etc/rc.dwith -o -i flags so listens on TCP/IPAlso, I should mention that the password I mentioned is NOT the password
for the local (Unix) pgsql account, but the password I set for thepostgresql database superuser, pgsql. That is the password I need toenter to get postgresql to start.Thanks,DW-
#!/bin/sh# $FreeBSD: ports/databases/postgresql74-server/files/pgsql.sh.tmpl,v1.17 2005/03/19 03:51:45 girgen Exp $## PROVIDE: postgresql# REQUIRE: LOGIN# KEYWORD: FreeBSD shutdown#
# Add the following line to /etc/rc.conf to enable PostgreSQL:##postgresql_enable=YES## optional#postgresql_data=/usr/local/pgsql/data#postgresql_flags=-w -s -m fast
## This scripts takes one of the following commands:## start stop restart reload status initdb## For postmaster startup options, edit ${postgresql_data}/postgresql.confprefix=/usr/local
. /etc/rc.subrload_rc_config postgresql# set defaultspostgresql_enable=${postgresql_enable:-NO}postgresql_flags=${postgresql_flags:--w -s -m fast}postgresql_user=pgsql
eval postgresql_data=${postgresql_data:-~${postgresql_user}/data}postgresql_class=${postgresql_class:-default}name=postgresqlrcvar=`set_rcvar`command=${prefix}/bin/pg_ctl
command_args=-D ${postgresql_data} ${postgresql_flags} $1extra_commands=reload initdbstart_cmd=postgresql_command startstop_cmd=postgresql_command stoprestart_cmd=postgresql_command restart
reload_cmd=postgresql_command reloadstatus_cmd=postgresql_command statusinitdb_cmd=postgresql_initdbpostgresql_command(){su -l ${postgresql_user} -c exec ${command} ${command_args}
}postgresql_initdb(){su -l -c ${postgresql_class} ${postgresql_user} -c exec${prefix}/bin/initdb -D ${postgresql_data}}run_rc_command $1-
Franco Bruno Borghesi wrote: This is not a PostgreSQL problem, it's the script you are using for startup that has some problem. The pg_hba method is for connection stablishment. PostgreSQL will start no matter what you put there.
 Startup scripts are usually run as root, and postgresql script should su to the postgresql user to start the database. I don't know what your script is doing, but root should be able to su to any user
 without password. Check your script, post it if you want. It would be usefull to know what system you are using also (linux/bsd/solaris/etc). 2005/5/20, Duane Winner 
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: hello, I've been using postgresql for about a year now, and am pretty
 comfortable with the basics, bu there has been something bugging me for a while now: I set the METHOD in pg_hba.conf to md5 so that a password is required from all users, from all hosts.
 The only problem is that if the server restarts, postgresql will not start until somebody goes to the console and enters the password for the pgsql account.
 Is there a solution for this solution? Thanks, DW ---(end of broadcast)--- TIP 6: Have you searched our list archives?
http://archives.postgresql.org http://archives.postgresql.org
---(end of broadcast)---TIP 3: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to 
[EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly

Re: [GENERAL] starting postgresql with pgsql password - workarounds?

2005-05-20 Thread Tom Lane

 # set defaults
 postgresql_enable=${postgresql_enable:-NO}
 postgresql_flags=${postgresql_flags:--w -s -m fast}

Try it without the -w ... that's probably causing it to try to connect
with psql.

Alternatively, set up a ~/.pgpass file for the postgres user (which
might be a reasonable thing anyway).

BTW, this script seems fairly brain-dead in assuming that the same
option flags should apply to all pg_ctl commands.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] starting

2005-05-08 Thread wayne schlemitz

I am interested in seting up postgres 7.4 or 8.0
which is best on SuSe 8.0 professional and where
should
I put the tar.gz for unzip and install?

Thank you.



Yahoo! Mail
Stay connected, organized, and protected. Take the tour:
http://tour.mail.yahoo.com/mailtour.html


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

[GENERAL] starting the database server

2004-11-30 Thread Nefnifi, Kasem




Hello,

I'm using a windows 
2000 advanced server, postgresql was installed and working fine, and I'm using 
pgadminIII. the database server cannot start and get error "is the postmaster 
running with -i on localhost 127.0.0.1 and accepting tcp/ip connection on the 
port 5432"

the last time, 
before this error,I've imported a data from a flat file into a table with 
the command copy. it was fine and I can see my data, after that I've closed the 
pgadmin and the application that I'm using. this application is via a web 
browser and you've a logout button, but I've closed the web browser without 
logging out. can that be the reason? I don't know.
restarting the 
server also didn't help, because the pgsql starts automatically when windows 
starts, it was always fine.
after looking, I 
find that it was a space problem on the drive where is installed windows and not 
the drive where is installed the pgsql and the database and also the application 
that I'm usingby the pgsql.
make some free 
spaces and increasing the virtual memory don't help.
now I've 
enoughfree space on all drives and the virtual memory is 2 times the 
physique memory of 1024. but still can't start the database.
when trying to start 
it via the services of windows, getan internal error thaterror 
inwindows or in the service...

any idea 
whyIcannot start the database and the service ?

thx


Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.

Re: [GENERAL] starting the database server

Nefnifi, Kasem wrote:
when trying to start it via the services of windows, get an internal
error that error in windows or in the service...
What error? What do your system logs say?
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] starting the database server

Nefnifi, Kasem wrote:
thanks Richard for the reaction,
bellow a print screen of the error that I get when I try to start the 
service from windows services control panel:
ole0.bmp
Try and stick to cutting and pasting text rather than embedding images - 
lots of people on the lists will be reading/posting in plain text rather 
than HTML. Also images use a lot more bandwidth than text.

Anyway - The service did not return an error. Seems unlikely that you 
wouldn't get some sort of error. Make sure your logging is turned on in 
postgresql.conf and then check your system logs for an error message - 
there should be something unless PG is failing *very* early in the startup.

If we still can't generate an error message, it might be worth trying to 
start the backend from the command-line.

The second error message you sent Connection refused just means the 
application couldn't contact the PG backend. We know it can't since the 
service isn't starting.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] starting the database server

2004-11-30 Thread Nefnifi, Kasem

Hi Richard,
bellow the text from the log file:

-- start log file --

30/11/2004  16:45:08PostgreSQL  Error   None0   N/A 
BAAN-AT-HOMEexecution of PostgreSQL by a user with administrative 
permissions is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.
 
30/11/2004  16:42:52SceCli  Warning None1202N/A 
BAAN-AT-HOMESecurity policies are propagated with warning. 0x534 : No 
mapping between account names and security IDs was done.

For best results in resolving this event, log on with a non-administrative 
account and search http://support.microsoft.com for troubleshooting 1202 
events.
A user account in one or more Group policy objects (GPOs) could not be resolved 
to a SID. This error is possibly caused by a mistyped nor deleted user account 
referenced in either the User Rights or Restricted Groups branch of a GPO.  To 
resolve this event, contact an administrator in the domain to perform the 
following actions:

1.Identify accounts that could not be resolved to a SID: From the command 
prompt, type: FIND /I Cannot find %SYSTEMROOT%\Security\Logs\winlogon.log 
The string following Cannot find in the FIND output identifies the problem 
account names.
Example: Cannot find JohnDough.
In this case, the SID for username JohnDough could not be determined. This 
most likely occurs because the account was deleted, renamed, or is spelled 
differently (e.g. JohnDoe).

2.Identify the GPOs that contain the unresolvable account name:
From the command prompt type FIND /I JohnDough 
%SYSTEMROOT%\Security\templates\policies\gpt*.*
The output of the FIND command will resemble the following:
-- GPT0.DOM
-- GPT1.DOM
SeRemoteShutdownPrivilege=JohnDough
This indicates that of all the GPO's being applied to this machine,  
the unresolvable account exists only in one GPO.  Specifically, the cached GPO 
named GPT1.DOM.
Now we need to determine the friendly name of this GPO in the next step.

3. Locate the friendly names of each of the GPOs that contain an unresolvable 
account name.  These GPOs were identified in the previous step.
From the command prompt, type: FIND /I [Mapping] 
%SYSTEMROOT%\Security\Logs\winlogon.log
The string following [Mapping] gpt?.dom = in the FIND output 
identifies the friendly names for all GPO's being applied to this machine.
Example: [Mapping] gpt1.dom = User Rights Policy
In this case, the GPO that contains the unresolvable account 
(gpt1.dom) has a friendly name of User Rights Policy.

4. Remove unresolved accounts from each GPO that contains an unresolvable 
account.
a. Start - Run - MMC.EXE
b. From the File menu select Add/Remove Snap-in...
c. From the Add/Remove Snap-in dialog box select Add...
d. In the Add Standalone Snap-in dialog box select Group Policy 
and click Add
e. In the Select Group Policy Object dialog box click the 
Browse button.
f. On the Browse for a Group Policy Object dialog box choose the 
All tab
g. Right click on the first policy identified in step 3 and choose edit
h.  Review each setting under Computer Configuration/ Windows 
Settings/ Security Settings/ Local Policies/ User Rights
 Assignment or Computer Configuration/ Windows Settings/ 
SecuritySettings/ Restricted Groups for accounts identified in step 1.
i. Repeat steps 3g and 3h for all subsequent GPOs identified in step 3. 


-- end log file --

Best Regards / Vriendelijke Groeten / Salutations Distinguées / Freundliche 
Grüße !!! 
Kasem NEFNIFI 
AtosOrigin Belgium N.V. 
Minervastraat  7 
1930 Zaventem (Belgium) 
Tel  : +32(0)2 712 28 30 
Fax : +32(0)2 712 28 63 
GSM   : +32 495 25 12 33 
Email : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]  
www.atosorigin.com http://www.atosorigin.com  



-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 2:17 PM
To: Nefnifi, Kasem
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] starting the database server


Nefnifi, Kasem wrote:
 thanks Richard for the reaction,
 bellow a print screen of the error that I get when I try to start the 
 service from windows services control panel:
 ole0.bmp

Try and stick to cutting and pasting text rather than embedding images - 
lots of people on the lists will be reading/posting in plain text rather 
than HTML. Also images use a lot more bandwidth than text.

Anyway - The service did not return an error. Seems unlikely that you 
wouldn't get some sort of error. Make sure your logging is turned on in 
postgresql.conf and then check your system logs for an error message - 
there should be something unless PG is failing *very* early in the startup.

If we

Re: [GENERAL] starting the database server

Nefnifi, Kasem wrote:
Hi Richard, bellow the text from the log file:
-- start log file --
30/11/2004  16:45:08PostgreSQL  Error   None0   N/A 
BAAN-AT-HOME
execution of PostgreSQL by a user with administrative permissions is
not permitted. The server must be started under an unprivileged user
ID to prevent possible system security compromise.  See the
documentation for more information on how to properly start the
server.
There you go - the user PostgreSQL tries to run under has administrative 
permissions. This isn't allowed for security purposes.

30/11/2004  16:42:52SceCli  Warning None1202N/A 
BAAN-AT-HOME
Security policies are propagated with warning. 0x534 : No mapping
between account names and security IDs was done.
For best results in resolving this event, log on with a
non-administrative account and search http://support.microsoft.com
for troubleshooting 1202 events. A user account in one or more
Group policy objects (GPOs) could not be resolved to a SID. This
error is possibly caused by a mistyped nor deleted user account
referenced in either the User Rights or Restricted Groups branch of a
GPO.  To resolve this event, contact an administrator in the domain
to perform the following actions:
What's more - there seems to have been a problem mapping user/group 
numbers to names. The rest of the message gives details of how to 
correct this.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] starting the database server

2004-11-30 Thread Nefnifi, Kasem

Hello,

but it has worked fine since the installation without any error until now and 
nothing has been changed in the system policy.
how it can something like this happened.
now the concrete solution, I've to follow the solution proposed in the log file.
which user should I use to start the database, if I take an only normal user, 
get the message error that I don't permissions, as administrator PostgreSql 
don't let me start the database. strange, because in all databases you've to be 
administrator to do something like except Postgresql.

what kind solution do you suggest to me and thx in advance. 

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 5:42 PM
To: Nefnifi, Kasem
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] starting the database server


Nefnifi, Kasem wrote:
 Hi Richard, bellow the text from the log file:
 
 -- start log file --
 
 30/11/200416:45:08PostgreSQL  Error   None0   N/A 
 BAAN-AT-HOME
 execution of PostgreSQL by a user with administrative permissions is
 not permitted. The server must be started under an unprivileged user
 ID to prevent possible system security compromise.  See the
 documentation for more information on how to properly start the
 server.

There you go - the user PostgreSQL tries to run under has administrative 
permissions. This isn't allowed for security purposes.

 30/11/200416:42:52SceCli  Warning None1202N/A 
 BAAN-AT-HOME
 Security policies are propagated with warning. 0x534 : No mapping
 between account names and security IDs was done.
 
 For best results in resolving this event, log on with a
 non-administrative account and search http://support.microsoft.com
 for troubleshooting 1202 events. A user account in one or more
 Group policy objects (GPOs) could not be resolved to a SID. This
 error is possibly caused by a mistyped nor deleted user account
 referenced in either the User Rights or Restricted Groups branch of a
 GPO.  To resolve this event, contact an administrator in the domain
 to perform the following actions:

What's more - there seems to have been a problem mapping user/group 
numbers to names. The rest of the message gives details of how to 
correct this.

-- 
   Richard Huxton
   Archonet Ltd

Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.


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

   http://archives.postgresql.org

Re: [GENERAL] starting the database server