Re: [GENERAL] using between with dates
On Thursday 29 April 2010 6:58:26 am Geoffrey wrote: > I'm trying the following: > > ship_date between '04/30/2010' AND '04/30/2010' + 14 > > But this returns: > > ERROR: invalid input syntax for integer: "04/30/2010" > > Can I use between with dates? > > -- > Until later, Geoffrey > > "I predict future happiness for America if they can prevent > the government from wasting the labors of the people under > the pretense of taking care of them." > - Thomas Jefferson ship_date between '04/30/2010' AND '04/30/2010'::date + 14 -- Adrian Klaver adrian.kla...@gmail.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] How to exit/abort from a function that returns VOID?
On Tuesday 04 May 2010 1:55:35 pm Andre Lopes wrote: > HI, > > I have a PLPgSQL function that return void but I need to exit the function > if some condition returns true. > > I have tried to do this, but I got an error: > > [code] > IF pVAL_CHAVE_2 > pVAL_CAMPO1 THEN > RAISE NOTICE 'O campo data fim tem de ser maior que o campo > data inicio.'; > RETURN 0; > END IF; > [/code] > > The error is the folowing: > > [quote] > ERROR: RETURN cannot have a parameter in function returning void at or > near "0" at character 1973 > [/quote] > > My question. How can I display the NOTICE and exit the function? > > Best Regards, RETURN; From here: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING -- Adrian Klaver adrian.kla...@gmail.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] cannot use column references in default expression?
On 05/28/2010 10:41 AM, Jignesh Shah wrote: Trigger should be the last solution. This used to be working but I think with latest postgresql upgrade, this stopped working. Might be someone around here knows whats going on here. Thanks, Jignesh On Fri, May 28, 2010 at 11:00 PM, Craig Ringer wrote: On 29/05/2010 1:20 AM, Jignesh Shah wrote: Could anyone please help me to get rid of following error? I want to set the 'ishuman' column based on the value of 'ID' column but it is not allowing me to do so. Any alternatives? Use a BEFORE trigger to set it. -- Craig Ringer What previous version? I just tried 8.3.5 and it did not work there either. -- Adrian Klaver adrian.kla...@gmail.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] there is a way to deactivate type validation on 8.3.1????
On 05/28/2010 11:20 AM, erobles wrote: hi! only for ask, there is a way to deactivate type validation, so i can do select rtrim(number_field) from table ; with no error and the message: "You might need to explicit type casts" this is postgres 8.3.1. What are you trying to do? I am trying to think what rtrim does on a number. -- Adrian Klaver adrian.kla...@gmail.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] there is a way to deactivate type validation on 8.3.1????
On 05/28/2010 11:45 AM, erobles wrote: On 05/28/2010 01:26 PM, Adrian Klaver wrote: On 05/28/2010 11:20 AM, erobles wrote: hi! only for ask, there is a way to deactivate type validation, so i can do select rtrim(number_field) from table ; with no error and the message: "You might need to explicit type casts" this is postgres 8.3.1. What are you trying to do? I am trying to think what rtrim does on a number. you are right, i'm trying to make a rtrim to a number. the reason of my question is we have a lot of apps with similar querys , when we have postgres7.2 this kind of querys just simply execute very well, so.. when change to postgres 8.3 this querys fail beacuse they needit an explicit cast. one solution is change the querys of apps and recompile, but time is short and the deadline is near. other solution is create a function to each explicit cast, but we dont have the time to research if there are more mismatch querys. so the easy way is deactivate the validation type ... so another For short term solution see here: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html -- Adrian Klaver adrian.kla...@gmail.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] there is a way to deactivate type validation on 8.3.1????
On Friday 28 May 2010 1:34:24 pm erobles wrote: > On 05/28/2010 03:18 PM, alvherre wrote: > > Excerpts from erobles's message of vie may 28 16:08:23 -0400 2010: > >> it did'nt work :-( , because the script is for pg 8.2 and i have pg > >> 7.2 and the table pg_cast doesn't exist. whatever is a good idea but i > >> need to know the table or tables on postgres 7.2. > > > > Of course it didn't work if you tried to run it in 7.2; you're supposed > > to run it in the 8.3 server, not the old one. The point is to make 8.3 > > sort-of compatible with the old behavior. > > did you see the script? > > didn't it work because the first command is a psql pointing a pg > server 8.2 > the second command is a psql pointing a pg server 8.3 > then the command are executed and get the differences > after that made the properly casting fucntions > > > this result ok , because the postgres 8.x.x have the same database > catalog in this case have the table pg_cast > so the query works OK. > > > > the problem in the postgres 7.2 doesn't exists the table pg_cast so i > can't get the information need it to get the differences > > so, i need modify the first command run it and get the info in the > same format to get it. I believe you are looking at the wrong file; the shell script. Try instead the other link http://wiki.postgresql.org/wiki/Image:Pg83-implicit-casts.sql It contains the functions you need. -- Adrian Klaver adrian.kla...@gmail.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] Installing version 8.4
On Saturday 29 May 2010 3:09:05 pm Bob Pawley wrote: > Hi > > I'm having problems installing PostgreSQL 8.4 on Windows 7. (See below) > > I downloaded the install from Enterprise DB. > > I attempted to install the same app on Windows XP. > > Both installs gave the same error message. > > Can anyone point me to a PostgreSQL installation for Windows that works? > > Can anyone suggest a way around these problems. (Enterprise support has yet > to answer). > > Bob > From here: http://www.enterprisedb.com/learning/pginst_guide.do Troubleshooting If you encounter any problems during installation, please check the logfile that is created in /tmp on Linux or Mac OS X or %TEMP% on Windows. The file will be called install-postgresql.log. The logfile may contain the superuser password you specified during the installation, which should be replaced before sharing the log with anyone. Did you look to see if there is anything in the above location that provides more information? -- Adrian Klaver adrian.kla...@gmail.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] Installing version 8.4
On Saturday 29 May 2010 4:26:08 pm Bob Pawley wrote: > Found it in XP it doesn't seem to exist in Windows 7. I can't even find Doc > and Settings in 7. > > It's a large file. I'm not sure what is needed but here is the latter part > of the file. > > Bob > Actually I was thinking you might want to browse the file to see if anything stands out. Most of the issues I have seen on this list relative to Windows installs relate to permissions, so that would be a good place to start. -- Adrian Klaver adrian.kla...@gmail.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] Autovacuum Daemon is Turned On by Default in Postgres 8.3.8?
On Wednesday 02 June 2010 5:40:58 pm Wang, Mary Y wrote: > Hi, > > I'm looking at my log file (see my log file below) and it seems like the > autovacuum daemon is turned on by default in Postgres 8.3.8. Does that > mean that I no longer need to manually invoke the autovacuum command > anymore? I used to do it manually in an older version of Postgres. > > tail postgresql-2010-06-02_00.log > DEBUG: autovacuum: processing database "template1" > DEBUG: server process (PID 31719) exited with exit code 0 > DEBUG: autovacuum: processing database "marydatabase" > DEBUG: server process (PID 31729) exited with exit code 0 > DEBUG: autovacuum: processing database "postgres" > DEBUG: server process (PID 31739) exited with exit code 0 > DEBUG: autovacuum: processing database "template1" > DEBUG: server process (PID 31741) exited with exit code 0 > DEBUG: autovacuum: processing database "marydatabase" > DEBUG: server process (PID 31753) exited with exit code 0 > > Thanks > Mary You talking about manually invoking Vacuum or autovacuum? -- Adrian Klaver adrian.kla...@gmail.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] Autovacuum Daemon is Turned On by Default in Postgres 8.3.8?
On Wednesday 02 June 2010 6:17:28 pm Wang, Mary Y wrote: > I used to execute the VACCUM and ANALYZE commands manually in a Postgres > 7.x.x version. So my question was that I assume that I no longer have to > perform those two commands manually anymore. I just wanted to be sure. > Mary > You don't have to, but you can still run those commands if needed. They are still there. -- Adrian Klaver adrian.kla...@gmail.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] How to remove the current database and populate the database with new data?
On Thursday 03 June 2010 4:05:14 pm Wang, Mary Y wrote: > Hi, > > I've some test data in a database and would like to delete that database > and clean everything that is associated with that database. Then I'd like > to populate the same database with different data. My plan is to: > (1) Remove the /usr/local/pgsql/data directory > (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt > (/tmp/indumpfile.txt has all the sql statements to restore the database) > (3) Restart the postgres server > > Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 > has the auto-vacuum daemon on to perform VACCUMs when it's necessary. Did > I miss any other steps for cleaning up? > > Please advise. > > Thanks > Mary Why not use DROP DATABASE? Removing the data directory removes the whole Postgres cluster, possibly including the config files. -- Adrian Klaver adrian.kla...@gmail.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] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8"
On Saturday 05 June 2010 7:07:35 am Wang, Mary Y wrote: > Thanks Alban, but I'm still confused. > I just read the PostgreSQL manual, and got the impression that UTF8 would > take care of ALL character sets, so I put SET client_encoding = 'UTF8' in > the dump file, and reload it again, then I got even more errors like: > psql:/tmp/060410finalsr15dump.txt:31430: ERROR: invalid byte sequence for > encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:31795: ERROR: > invalid byte sequence for encoding "UTF8": 0x93 > psql:/tmp/060410finalsr15dump.txt:31826: ERROR: invalid byte sequence for > encoding "UTF8": 0x85 psql:/tmp/060410finalsr15dump.txt:31827: ERROR: > invalid byte sequence for encoding "UTF8": 0x92 > psql:/tmp/060410finalsr15dump.txt:31874: ERROR: invalid byte sequence for > encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:32111: ERROR: > invalid byte sequence for encoding "UTF8": 0x93 > psql:/tmp/060410finalsr15dump.txt:32112: ERROR: invalid byte sequence for > encoding "UTF8": 0x92 > > What is the client encoding that I should set to so that it would take care > all character sets? > http://www.postgresql.org/docs/8.3/interactive/multibyte.html > > Any suggestions? > Thanks > Mary > I am afraid it does not work that way. You have to think translation tables. In order for the process to work Postgres needs to know the correct encoding it is receiving in order to make the translation to UTF8. Much the same problem as giving someone who knows how to translate French to English, a document that contains a mixture of words in different languages. If they don't know what the words are they cannot be translated. -- Adrian Klaver adrian.kla...@gmail.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] UUID column as pimrary key?
On Thursday 06 January 2011 7:14:00 am Bill Moran wrote: > In response to Scott Ribe : > > On Jan 6, 2011, at 1:52 AM, Stuart Bishop wrote: > > > If you are looking at these extreme > > > improbabilities, your SERIAL isn't guaranteed unique either when you > > > take into account cosmic rays flipping the right bits in your ECC > > > memory or on your disk platter. > > > > Yes, that's rather the point, the probability is so extremely low that it > > in most cases it should be treated as 0. Some people seem to have a > > problem wrapping their heads around relative magnitudes that extreme. > > > There. I Godwined the damn thing. > > -- > Bill Moran Maybe a wrap up is in order:) As I said earlier this is one of those arguments that could go forever because everyone is right, so to summarize: 1) UUIDs can have a very to extremely large namespace but less than infinite. 2) There are other alternatives i.e SERIAL 3) Managing the above is based on the interaction of three components - software,hardware,wetware(people). Any one of which can have a weakness and in combination their are many permutations. 4) DBAs need to plan for the worse. Worse being somewhat contextual. Real time control of a nuclear plant versus Web social media. Choosing a unique number generator and dealing with possible collisions is contingent on this context. -- Adrian Klaver adrian.kla...@gmail.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] pg_dump and database size question
On Thursday 06 January 2011 5:58:13 am Birta Levente wrote: > Hi, > > > I have postgres 9.0.2 server with a 2.4GB database. > After pg_dump of the database, the size increased with aprox 200MB ... why? > I make some tests and the total_relation_size is increased, but the > relation_size not. > > thanks > > Birta Levente Are you saying the existing database increases in size after the dump or that when you dump/restore the restored db is greater in size than what it was previously? Also if it was a dump/restore where you changing Postgres versions? In any case total_relation_size includes more information than relation_size; notably TOAST space, indexes, and two more of the data forks(main,fsm,vm). So the difference is in one of those. See here for more info: http://www.postgresql.org/docs/9.0/interactive/functions-admin.html As to why, that probably depends on the answer to my previous questions. -- Adrian Klaver adrian.kla...@gmail.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] Backup and restore sequences
On Friday 07 January 2011 7:46:31 am gvim wrote:
> PostgreSQL 9.0.1/pgAdminIII 1.12.1
>
> I want to copy selected tables from one database to another and maintain
> the sequences which I originally setup with:
>
> CREATE SEQUENCE venues_id_seq START WITH 1122;
> ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
>
> ... along with their current values, which have been augmented since the
> database was setup. When I backup via pgAdminIII the sequences are not even
> included. I also can't find anything in:
>
> man pg_dump
>
> ... which specifies sequences.
>
> gvim
Details below. When you dump a specific table using -t it will not
automatically dump dependent objects. -t can be used to dump a sequence because
they are just a special type of table.
From:
http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
"
-t table
--table=table
Dump only tables (or views or sequences) matching table. Multiple tables
can
be selected by writing multiple -t switches. Also, the table parameter is
interpreted as a pattern according to the same rules used by psql's \d commands
(see Patterns), so multiple tables can also be selected by writing wildcard
characters in the pattern. When using wildcards, be careful to quote the
pattern if needed to prevent the shell from expanding the wildcards.
The -n and -N switches have no effect when -t is used, because tables
selected by -t will be dumped regardless of those switches, and non-table
objects will not be dumped.
Note: When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon. Therefore, there
is no guarantee that the results of a specific-table dump can be successfully
restored by themselves into a clean database.
Note: The behavior of the -t switch is not entirely upward compatible
with pre-8.2 PostgreSQL versions. Formerly, writing -t tab would dump all
tables named tab, but now it just dumps whichever one is visible in your
default search path. To get the old behavior you can write -t '*.tab'. Also,
you must write something like -t sch.tab to select a table in a particular
schema, rather than the old locution of -n sch -t tab. "
--
Adrian Klaver
adrian.kla...@gmail.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] OOO and postgres
On 01/07/2011 09:40 AM, Bernhard Rohrer wrote: Hi guys I am using the postgres driver for OOO and just ran into the following error: Error code: 1 If you are referring to OpenOffice and the native SDBC driver then from the docs: http://dba.openoffice.org/drivers/postgresql/index.html#features "data types like clobs, blobs and arrays are not yet supported. The whole datatype handling for non-standard datatypes is crippled currently, here needs to be developed a concept first. -- Adrian Klaver adrian.kla...@gmail.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] OOO and postgres
On 01/07/2011 10:16 AM, Bernhard Rohrer wrote:
What I am not getting is - as far as I can see none of the fields _is_
an array! So why would this error message appear?ion
The "type" field is an array type because of the square brackets after
the data type.
"type" character varying[] NOT NULL
The INSERT is not inserting the VALUES for an array correctly. To get
back to my original question, what driver are you using? If it is the
native SDBC one it does not know about arrays and is probably the cause
of the problem.
On 07/01/11 17:40, Bernhard Rohrer wrote:
Hi guys
I am using the postgres driver for OOO and just ran into the following
error:
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERROR: array value must start with "{"
or dimension information
LINE 1: ...O "public"."Bladetypes" ( "ID","type") VALUES ( '1','Knife')
^
(caused by statement 'INSERT INTO "public"."Bladetypes" ( "ID","type")
VALUES ( '1','Knife')')
the table looks like this:
CREATE TABLE "Bladetypes"
(
"ID" integer NOT NULL,
"type" character varying[] NOT NULL,
CONSTRAINT "Bladetypes_pkey" PRIMARY KEY ("ID")
Thanks
Bernhard
--
Adrian Klaver
adrian.kla...@gmail.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] Backup and restore sequences
On 01/07/2011 08:26 AM, gvim wrote: On 07/01/2011 15:58, Adrian Klaver wrote: Details below. When you dump a specific table using -t it will not automatically dump dependent objects. -t can be used to dump a sequence because they are just a special type of table. From: http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html " -t table --table=table Thanks. I just found this and it works great but wish pgAdmin had something similar. gvim I broke down and installed pgAdmin. You can do what you want by finding the sequence in the object browser on the left and right clicking and selecting CREATE script. -- Adrian Klaver adrian.kla...@gmail.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] Inconsistent time interval formatting
On 01/13/2011 12:55 PM, Allen Chen wrote: That won't really help. The fundamental point here is that '1 day' is not the same concept as '24 hours', because of DST changes; and the interval type treats them as different. If you don't care about that, you can use justify_hours (I think that's the right function) to smash them to the same thing. But I suspect the OP's real complaint would be better solved by use of to_char() to produce an output format that includes zeroes instead of dropping fields that are zero. regards, tom lane Hi Tom, I don't understand how DST changes matter for a time interval or how that could even be factored into calculations. Could you elaborate on that? I had a query today that returned an interval of 70:23:06.935933. Wouldn't that be at least two days regardless of DST? Thanks for shining the light on justify_hours, though. I did not know that function existed. That does give me a way to have consistent output for reporting. Thanks to everyone who replied! -Allen I think to help with this we will need the complete cycle, in other words the queries you are using to generate the intervals as well as the resultant intervals. -- Adrian Klaver adrian.kla...@gmail.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] pg_db_role_setting info not dumped
Postgres 9.0.2 I used the ALTER DATABASE command to create a database specific SET configuration. The problem is that information does not seem to be dumped. I have tried pg_dumpall -g, pg_dumpall as well as a pg_dump for the individual database. Am I missing something? -- Adrian Klaver adrian.kla...@gmail.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] pg_db_role_setting info not dumped
On Friday 14 January 2011 2:52:18 pm Tom Lane wrote: > Adrian Klaver writes: > > Postgres 9.0.2 > > I used the ALTER DATABASE command to create a database specific SET > > configuration. The problem is that information does not seem to be > > dumped. I have tried pg_dumpall -g, pg_dumpall as well as a pg_dump for > > the individual database. Am I missing something? > > Should be dumped by pg_dumpall (without the -g option). > > There has been debate about whether plain pg_dump should deal with such > things, but nothing's been changed yet. > > regards, tom lane pg_dumpall worked. I missed it on my previous look. I vote for having it included in the plain pg_dump output. -- Adrian Klaver adrian.kla...@gmail.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] HA solution
On Saturday 15 January 2011 10:07:14 am Jaiswal Dhaval Sudhirkumar wrote: > Thanks for your support. > > We have power full HP servers with lots of CPU cores, I/O bandwidth and > memory too. > > Actually I will give you the environment details, which will help you to > understand. > > It is a huge set-up where we have a DC & DR. There will be lots of daily > edit and read hits. Also there would be lots of read hits from reporting > perspective too. Therefore, I thought of to keep one node for edit and > other one for read (OLTP vs REPORTING) kind of structure for DC & if it > goes down DR will take care. However in your suggested structure there will > be only one active node at a time in DC. Even standby database would be in > recovery mode. > > Initially I thought of the set-up slony replication for (OLTP vs REPORTING) > & pgpool replication for (OLTP vs OLTP) but there will be very huge GIS > database size & they agreed only on cluster set-up. > > I really need thoughts/comments/help from experts. First a data directory cannot be shared between database clusters. To put it another away each database cluster you initdb will need to have its own data directory. Second with Postgres 9.0+ there is the concept of hot standby whereby the standby cluster is available for read only operations while it is in recovery mode. For an idea of what is possible you might to take a look at this blog: http://blog.2ndquadrant.com/en/2011/01/easier-postgresql-90-clusters.html Conclusion. Pulling together what has already been posted. If you want to run two Postgres instances you will need to have two copies of the data. If you want one copy of the data you will have to work with one instance of Postgres. > > > -- > Thanks & Regards > Dhaval Jaiswal > -- Adrian Klaver adrian.kla...@gmail.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] HA solution
On Sunday 16 January 2011 7:19:49 am Jaiswal Dhaval Sudhirkumar wrote: > Thanks for your great comments. > > I have gone through suggested link & the streaming replication with 9.0+. > (We are using PostgreSQL 8.4. Not to worry I will migrate it to 9.0+) > > It seems to me that if i will go with this Active/Hot stand by(stream) > architecture, I can generate the report from my standby database; which is > in readable mode. Isn't it? Yes. > > Can i take the dump from my standby database through pg_dump? See here for answer and a suggestion: http://archives.postgresql.org/pgsql-general/2010-07/msg00767.php > > Is this standby comfortable with Big datatypes like (BLOB, CLOB, GIS, etc.) > & partitioning table? The built-in replication works at the binary level and ships whatever happens on A to B. For a more in depth look see this Wiki page: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#What_Can_You_Do_With_Binary_Replication.3F > > > -- > Thanks & Regards > Dhaval Jaiswal > -- Adrian Klaver adrian.kla...@gmail.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] Why can't I change a password
On Sunday 16 January 2011 1:49:43 pm Steve Litt wrote: > On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote: > > If you have configured PG to listen on a TCP/IP port (5432 by default), > > you can also do: > > > >psql -U postgres -h localhost super > > > > Ray. > > Thanks Ray, > > You were so close! The command that works is this: > > psql -U super -h localhost super > > That's because the super database is owned by the super user, in this > particular case. Interestingly enough, even though I've set my port to be > 5433 instead of 5432, it wasn't necessary for me to add -p 5433. The change will not take effect until you restart the server. At that point you will need to specify the port. > > Your method has the big benefit of being able to supervise from a Postgres > Superuser who doesn't have a Linux account (super, in this case). Thanks > for the info! See here for a more in depth look at client authentication: http://www.postgresql.org/docs/9.0/interactive/client-authentication.html > > SteveT > > Steve Litt > Recession Relief Package > http://www.recession-relief.US > Twitter: http://www.twitter.com/stevelitt -- Adrian Klaver adrian.kla...@gmail.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] Why can't I change a password
On Sunday 16 January 2011 1:49:43 pm Steve Litt wrote: > On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote: > > If you have configured PG to listen on a TCP/IP port (5432 by default), > > you can also do: > > > >psql -U postgres -h localhost super > > > > Ray. > > Thanks Ray, > > You were so close! The command that works is this: > > psql -U super -h localhost super > > That's because the super database is owned by the super user, in this > particular case. Interestingly enough, even though I've set my port to be > 5433 instead of 5432, it wasn't necessary for me to add -p 5433. Just realized you do have a server running at 5433 per this from one of your previous posts: slitt@mydesk:~$ psql -U postgres -h localhost -p 5433 super Password for user postgres: psql: FATAL: password authentication failed for user "postgres" FATAL: password authentication failed for user "postgres" slitt@mydesk:~$ There would have been a different error if there was no server listening on that port. It would seem you have more than one Postgres server running. > > Your method has the big benefit of being able to supervise from a Postgres > Superuser who doesn't have a Linux account (super, in this case). Thanks > for the info! > > SteveT > > Steve Litt > Recession Relief Package > http://www.recession-relief.US > Twitter: http://www.twitter.com/stevelitt -- Adrian Klaver adrian.kla...@gmail.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] HA solution
On Monday 17 January 2011 5:43:19 am Jaiswal Dhaval Sudhirkumar wrote: > http://wiki.postgresql.org/wiki/Streaming_Replication > > I have gone through the above link. Current streaming replication provides > Asynchronous based solution & synchronous solution will come with version > 9.1 > > Can some one please give me an approximate idea when version 9.1 will be > release? > See here to keep up on development schedule: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Development_Plan FYI 9.0 was released 09/20/2010 which would give a rough date of September/October of this year per above. Be aware that not all items in development make it to the next release. > > -- > > Thanks & Regards > Dhaval Jaiswal > -- Adrian Klaver adrian.kla...@gmail.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] Install PostgreSQL as part of a desktop application, but how to coop with existing installations?
On Monday 17 January 2011 8:11:20 am Jensen Somers wrote: > On 17/01/2011 16:46, Andrew Sullivan wrote: > > On Mon, Jan 17, 2011 at 04:31:45PM +0100, Jensen Somers wrote: > >> But, from your initial reply I understood that a user can simply browse > >> to my database installation folder (e.g.: C:/ProgramData/MyApp/data), > >> read out and/or modify a configuration file and he can access the entire > >> database and modify the data. And that's what I want to prevent. > > > > Dunno about CE, but this is also trivial using SQLite. > > > > But also, given your needs, I wonder pretty seriously whether Postgres > > is the right thing for your application. It sounds like this is > > always single-user with no contention. Postgres is a bad fit for > > that. Use SQLite or one of the other things that target embedded use. > > > > A > > The problem is that the library I want to use does not support SQLite as > a database provider and SQL CE is limited to a database file of 4GB and > lacks several interesting SQL features, hence why I was interested in > using PostgreSQL. > > - Jensen Out of curiosity what library is that? -- Adrian Klaver adrian.kla...@gmail.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] Install PostgreSQL as part of a desktop application, but how to coop with existing installations?
On 01/17/2011 10:21 AM, Jensen Somers wrote: The ORM framework I want to use is http://dataobjects.net/ - Jensen Hmm, sees you might have to be patient:) From the their manual: Upcoming storage providers include: * Built-in file system database (i.e. regular embedded database) Any bets on whether that is going to be Sqlite? -- Adrian Klaver adrian.kla...@gmail.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] Error during a dump (ts_selectivity, not found)
On Wednesday 19 January 2011 4:57:42 am Ahmed Ossama wrote: > Hello all, > > I am trying to restore a database on a server from a dump taken from a > different server, but I get the following error: > > > I searched for the library ts_selectivity, but all in vain. > > Any advice/hint is very much appreciated. Are you by any chance trying to restore a dump taken from a pre-8.3 server with Full Text Search to 8.3+ one? If so in 8.3 Full Text Search moved from being a contrib module to being integrated in the server. See here for some tips on how to handle if that is the case: http://www.postgresql.org/docs/8.3/interactive/textsearch-migration.html > > Best Regards, > Ahmed Ossama -- Adrian Klaver adrian.kla...@gmail.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] How to fire triggers just on "top" level DML
On Wednesday 19 January 2011 1:59:48 pm Kevin Grittner wrote: > We've been running for about ten years on a framework which fires > triggers similar to database triggers in a Java tier close to the > database, and we're now trying to convert these to actual PostgreSQL > database triggers. Our biggest hitch at the moment is that we > defined a class of triggers we called "top" triggers, which only > fire from DML submitted by the application, not from DML issued by > other triggers. > > One significant use of this is to block direct modification of > summary data (either selected columns or entire tables) which are > supposed to be trigger maintained. It's not immediately obvious how > to accomplish this within PostgreSQL, although I'm probably missing > something. We're not tied to any particular methodology -- a > TG_DEPTH variable, if it existed, would do fine, for example. > > Any suggestions? > > -Kevin The only thing I can think of off the top of my head is to you trigger arguments and then use the below to test: TG_ARGV[] Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value. -- Adrian Klaver adrian.kla...@gmail.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] How to fire triggers just on "top" level DML
On Wednesday 19 January 2011 2:20:35 pm Kevin Grittner wrote: > Adrian Klaver wrote: > > TG_ARGV[] > > > > Data type array of text; the arguments from the CREATE > > TRIGGER statement. > > Thanks for the suggestion, but I don't think this does what I need. > I need to know whether the trigger was *fired* from inside another > trigger, not something about the creation of trigger. > > -Kevin I misunderstood I thought there where 'top' triggers and other triggers and using arguments would be way to tag them. -- Adrian Klaver adrian.kla...@gmail.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] Problems with pg_dump/restore and OIDs
On Thursday 20 January 2011 6:59:08 am Khristian wrote: > Hi, > I'm trying to backup a schema in a database which uses OIDs. Using > pg_dump | psql works fine, but I need to backup it to a file. So I > tried using pg_dump like this: > pg_dump -h localhost -U usr -F c -f dump.sql -o -n my_schema -b > source_db_name And then restoring it with: > pg_restore -d target_db_name -h localhost -U usr -W -F c dump.sql > And it always fails when restoring some of the OIDs or because of > foreign key restrictions. > > How can I backup this successfully? > > Thanks, > -- > Khristian Alexander Schönrock One potential problem is that you are restricting the dump to one schema. From the docs: "Note: When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database. " Similar issue possibly for the OIDS. Unless you are dumping to an empty database it is quite possible that the OIDS are already in use. -- Adrian Klaver adrian.kla...@gmail.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] Server stops responding in every week
On Friday 21 January 2011 7:13:23 am Andy Colson wrote: > On 1/21/2011 7:16 AM, Andrus wrote: > > PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit > > is installed in Windows 2008 server and servers 23 users in LAN 10 hours > > per work day. > > > > Approx once per week server stops responding: Users are waiting 4 > > minutes for query to complete but nothing happens. > > > > If postgres service is re-started in Windows Control panel, it continues > > working for a week and same issue appears again. > > Server and windows event log does not contian any information about > > issue. > > > > > > How to fix of diagnose it ? > > Should I move to 32 Postgres 9.0 server or other idea ? > > > > Andrus. > > Does PG start swapping to disk? > Does ram using increase over the week? > Have you looked at pg_stat_activity and pg_locks? > Do you see "Idle in transaction" (probably in task manager)? > > -Andy To add to this. Is there a backup being done? Maybe not pg_dump but some Windows backup program? -- Adrian Klaver adrian.kla...@gmail.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] error while trying to change the database encoding on a database
On Monday 24 January 2011 6:38:55 am Geoffrey Myers wrote: > We need to change the database encoding on our databases as they were > created with the wrong encoding. They were created as SQL_ASCII and we > are changing them to UTF8. > > When testing this Friday, I received the following error: > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE > DATA cust postgres > pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence > for encoding "UTF8": 0xb0 > HINT: This error can also happen if the byte sequence does not match > the encoding expected by the server, which is controlled by > "client_encoding". > CONTEXT: COPY cust, line 778 ^^^ In the COPY command for that table. > > Is there any easy way to figure out which record caused this error? > > Thanks. > > -- > Until later, Geoffrey -- Adrian Klaver adrian.kla...@gmail.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] error while trying to change the database encoding on a database
On Monday 24 January 2011 7:57:52 am Geoffrey Myers wrote: > Adrian Klaver wrote: > > On Monday 24 January 2011 6:38:55 am Geoffrey Myers wrote: > >> We need to change the database encoding on our databases as they were > >> created with the wrong encoding. They were created as SQL_ASCII and we > >> are changing them to UTF8. > >> > >> When testing this Friday, I received the following error: > >> > >> pg_restore: [archiver (db)] Error while PROCESSING TOC: > >> pg_restore: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE > >> DATA cust postgres > >> pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence > >> for encoding "UTF8": 0xb0 > >> HINT: This error can also happen if the byte sequence does not match > >> the encoding expected by the server, which is controlled by > >> "client_encoding". > >> CONTEXT: COPY cust, line 778 > > > > ^^^ In the COPY command for that table. > > I picked up ont that, but the dump is binary, thus I can not view the > actual code. Actually you can :) I should have mentioned it before. You can have pg_restore restore to a file instead of a database by using the -f switch. When you do that it creates plain text output. You could restore the entire dump to the file or use the -t switch to get only the table you need. > > >> Is there any easy way to figure out which record caused this error? > >> > >> Thanks. > >> > >> -- > >> Until later, Geoffrey > > -- > Until later, Geoffrey > > "I predict future happiness for America if they can prevent > the government from wasting the labors of the people under > the pretense of taking care of them." > - Thomas Jefferson -- Adrian Klaver adrian.kla...@gmail.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] error while trying to change the database encoding on a database
On Monday 24 January 2011 8:06:38 am Geoffrey Myers wrote: > Adrian Klaver wrote: > > On Monday 24 January 2011 7:57:52 am Geoffrey Myers wrote: > >> Adrian Klaver wrote: > >>> On Monday 24 January 2011 6:38:55 am Geoffrey Myers wrote: > >>>> We need to change the database encoding on our databases as they were > >>>> created with the wrong encoding. They were created as SQL_ASCII and > >>>> we are changing them to UTF8. > >>>> > >>>> When testing this Friday, I received the following error: > >>>> > >>>> pg_restore: [archiver (db)] Error while PROCESSING TOC: > >>>> pg_restore: [archiver (db)] Error from TOC entry 5225; 0 16990 TABLE > >>>> DATA cust postgres > >>>> pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence > >>>> for encoding "UTF8": 0xb0 > >>>> HINT: This error can also happen if the byte sequence does not match > >>>> the encoding expected by the server, which is controlled by > >>>> "client_encoding". > >>>> CONTEXT: COPY cust, line 778 > >>> > >>> ^^^ In the COPY command for that table. > >> > >> I picked up ont that, but the dump is binary, thus I can not view the > >> actual code. > > > > Actually you can :) I should have mentioned it before. You can have > > pg_restore restore to a file instead of a database by using the -f > > switch. When you do that it creates plain text output. You could restore > > the entire dump to the file or use the -t switch to get only the table > > you need. > > Thanks for the suggestion. As it stands, we are getting different > errors for different hex characters, thus the solution we need is the > ability to identify the characters that won't convert from SQL_ASCII to > UTF8. Is there a resource that would identify these characters? > Well the issue is that SQL_ASCII is not an encoding. From the docs: http://www.postgresql.org/docs/9.0/interactive/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED "Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters. " What you need to do is determine what applications where putting data into the database and what encoding they are using. I ran into this a couple of years back with an app that was using WIN1252 for data being inserted into a couple of tables in a SQL_ASCII database . Once I knew the encoding I dumped the table schema only for those tables into a new UTF8 database. Using psql I set the client_encoding to WIN1252 and then used \i to pull in a plain text data only dump for each table. > > -- > Until later, Geoffrey > > "I predict future happiness for America if they can prevent > the government from wasting the labors of the people under > the pretense of taking care of them." > - Thomas Jefferson -- Adrian Klaver adrian.kla...@gmail.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] error while trying to change the database encoding on a database
On 01/24/2011 09:16 AM, Geoffrey Myers wrote: We hope to identify the characters and fix them in the existing database, then convert. It appears to be very limited, but it would help if there was some way to identify these characters outside of simply doing the reload of the data and finding the errors. Hence the reason I asked about a resource that might identify the characters. The problem is that from the standpoint of the SQL_ASCII database there is nothing wrong with the characters per se. AFAIK there is no built in function to validate characters. The reason is that valid is determined by the encoding and if you know the encoding then you really don't need to determine validity. If you want to see one way others have tackled this, search on iconv in the mailing list archive. This requires working on an external copy of the data and knowing something about the encodings involved. The nearest I could ever find to an encoding detector is: http://chardet.feedparser.org/ It is a Python program and the encodings it detects are limited but it might work for you. Given all the above, when I was faced with the problem you are facing I found it easiest to make an educated guess as to the original encoding and then do test restores with client_encoding set to my guess. -- Until later, Geoffrey -- Adrian Klaver adrian.kla...@gmail.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] error while trying to change the database encoding on a database
On 01/24/2011 10:57 AM, Geoffrey Myers wrote: Adrian Klaver wrote: On 01/24/2011 09:16 AM, Geoffrey Myers wrote: We hope to identify the characters and fix them in the existing database, then convert. It appears to be very limited, but it would help if there was some way to identify these characters outside of simply doing the reload of the data and finding the errors. Hence the reason I asked about a resource that might identify the characters. The problem is that from the standpoint of the SQL_ASCII database there is nothing wrong with the characters per se. AFAIK there is no built in function to validate characters. The reason is that valid is determined by the encoding and if you know the encoding then you really don't need to determine validity. If you want to see one way others have tackled this, search on iconv in the mailing list archive. This requires working on an external copy of the data and knowing something about the encodings involved. The nearest I could ever find to an encoding detector is: http://chardet.feedparser.org/ It is a Python program and the encodings it detects are limited but it might work for you. Given all the above, when I was faced with the problem you are facing I found it easiest to make an educated guess as to the original encoding and then do test restores with client_encoding set to my guess. Understood. We had figured the problem to be small, and it appears it is and thus felt we could address it a character at a time. Then get this error: pg_restore: [archiver (db)] Error from TOC entry 5258; 0 17549 TABLE DATA fax postgres pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xe28053 That hex value doesn't translate to a single character. I've dumped the data to a file as you suggested, but reviewing the identified line brings no joy. The only thing I can think of is to use iconv like: iconv -c -t utf8 -f utf8 -o converted_txt.txt 'original.txt' where original.txt is your plain text data dump. The -c switch causes iconv not to convert any illegal characters. You could then run a diff against converted_txt.txt and 'original.txt' to see what characters in the original text are causing the problem. -- Adrian Klaver adrian.kla...@gmail.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] Install Pgadmin3 1.12 on ubuntu 10.4 lucid client without postgresql server install
On 01/26/2011 09:23 AM, margaretgil...@chromalloy.com wrote: Correction on the commands I quoted. The first one should have been for the repository. >And you need PostgreSQL libpq to use pgAdmin. That's all you need. >Guillaume Since I cannot use the Ubuntu repository what is the package name for the postgresql 9 library? If I take the postgresql-9.0 out of the install command on the link I sent and run the commands below add-apt-repository ppa:pitti/postgresql sudo apt-get install postgresql-client-9.0 wget http://wwwmaster.postgresql.org/redir/407/h/pgadmin3/release/v1.12.1/src/pgadmin3-1.12.1.tar.gz tar xvfz pgadmin3-1.12.1.tar.gz cd pgadmin3-1.12.1 ./configure I get the error configure: error: Could not find your PostgreSQL installation. You might need to use the --with-pgsql=DIR configure option Well according to the below there is a Lucid deb for pgAdmin 1.12 at that repository: https://launchpad.net/~pitti/+archive/postgresql Try sudo apt-get install pgadmin3 Thanks, Margaret -- Adrian Klaver adrian.kla...@gmail.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] Dumpall without OID
On 01/27/2011 04:52 AM, Girts Laudaks wrote: Hi, What could be the possible damage if a database is migraged without the -o (OID) option? Integrity of data? User OIDs are deprecated,so the main problem is whether you are currently using them as foreign keys. If you do not specify the -o switch and have OIDs specifically defined for a table(not the default) then new ones will be generated when the data is restored. If your schema/app depend on the old numbers then it would be a problem. What are the options to solve this problem if it is migrated this way? Shouldn't pg_dumpall work like an exact copy of db? What could have gone wrong if only these commands were used? pg_dumpall -v -U postgres | gzip > /var/lib/pgsql/dump.data.gz gunzip -c /var/lib/pgsql/dump.data.gz | psql -U postgres >> /tmp/import.log They were used to migrate from PG8.4 to PG9 Regards, Girts -- Adrian Klaver adrian.kla...@gmail.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] Dumpall without OID
On Friday 28 January 2011 4:54:18 am Girts Laudaks wrote: > Well, seems that everything is OK. There are no OIDs used from the > application side but they still appear in the database tables, this was > what made the confusion. > > Thanks, > G. > OIDS on user tables have not been on by default since 8.0. Possible reasons why they exist: If the tables have been around since 8.0- OIDS will carry on with them. The table CREATE statements have the WITH OIDS clause. In postgresql.conf the default_with_oids setting is set on. In addition to the point I made yesterday, some old database drivers rely on OIDS to determine uniqueness. If you are positive that they are not needed you can use the ALTER TABLE table_name SET WITHOUT OIDS to remove the OID column from a table (http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html) -- Adrian Klaver adrian.kla...@gmail.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] Some Problems - Shall I reinstall the DB?
On 01/31/2011 12:49 PM, vwu98...@lycos.com wrote: The 8.x version of PostgreSQL has been installed on my PC for quite few years. It has been problem free until recently. The first problem I have is that a connection can't be established after the PC just starts up although the DB is started. I have to restart it to solve this problem each time after my PC is up. The other problem, I just find this morning, is that table ID generation. Are you talking about the Hibernate connection below? Can you connect using psql? A table ID is defined as the following: id SERIAL UNIQUE primary key, When I save a new entry to this table, I get an error as org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save() This seems to be a Hibernate problem more than a Postgres one. Has there been a upgrade to Hibernate recently? from Hibernate. No all table demonstrate this error. Shall I reinstall PostgreSQL for resolve those two problem? Thanks, - v -- Adrian Klaver adrian.kla...@gmail.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] Some Problems - Shall I reinstall the DB?
On 01/31/2011 01:18 PM, vwu98...@lycos.com wrote: The DB connection problem has nothing to do with Hibernate. As I have said that the problem can be solved by restarting the DB. This problem occurs with my application as well as DdVisualizer. So know we know it is a generic connection problem. Is there information in the Postgres logs that might be helpful? And as I have mentioned, the id error doesn't occur to other table. Again is there any information generated in the logs when you try to connect to that table? Another thing to check is whether the max id in the table is greater then the last value for the sequence associated with that table. Could be id(s) got inserted without using the sequence. At this time reinstalling the DB would seem to be excessive. There is no guarantee that the problem won't just appear again with out knowing what is in the first place. -- Adrian Klaver adrian.kla...@gmail.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] Almost full pg_xlog/
On Monday, January 31, 2011 3:32:16 pm Jeff Ross wrote: > Hi, > > I started trying to setup streaming replication here at work the first > of December but I didn't actually succeed until today. Too many other > crises to attend to ;-) > > In the meantime, I now have 242 16MB files in pg_xlog/. The > archive_status directory in pg_xlog/has a matching .done file for all > but the most recent of those pg_xlog files. > > Am I correct in thinking that I can delete most if not all of the > pg_xlog/ files that have a matching .done entry in > pg_xlog/archive_status/ ? Or is there a better way? > > Sorry in advance if this is in the docs somewhere--I haven't found it yet. > > Thanks, > > Jeff Ross What do you have wal_keep_segments set to in postgresql.conf? See here for more detail: http://www.postgresql.org/docs/9.0/interactive/runtime-config-wal.html#RUNTIME- CONFIG-REPLICATION -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] cast problem in Postgresql 9.0.1
On Monday, January 31, 2011 10:14:29 pm AI Rumman wrote: > I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. > > I have a table "testtab" > \d testtab > id int, > hours varchar > > When I execute the following: > select sum(hours) from testtab > I get cast error. Try: select sum(hours::int) from testtab; > > Then, > > I created following IMPLICIT CAST functions in my DB => > > CREATE FUNCTION pg_catalog.integer(varchar) RETURNS int4 STRICT IMMUTABLE > LANGUAGE SQL AS 'SELECT int4in(varcharout($1));'; > CREATE CAST (varchar AS integer) WITH FUNCTION pg_catalog.integer(varchar) > AS IMPLICIT; > > CREATE FUNCTION pg_catalog.smallint(varchar) RETURNS smallint STRICT > IMMUTABLE LANGUAGE SQL AS 'SELECT int2in(varcharout($1));'; > CREATE CAST (varchar AS smallint) WITH FUNCTION > pg_catalog.smallint(varchar) AS IMPLICIT; > > Now, the above query works, but > SELECT COALESCE(hours,0) from testtab > failed. > > Any idea why? -- Adrian Klaver adrian.kla...@gmail.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] Some Problems - Shall I reinstall the DB?
On Tuesday, February 01, 2011 12:03:20 pm vwu98...@lycos.com wrote: > When I try to access PG from PC command prompt with the psql command, I get > the following error message: > > psql: server closed the connection unexpectedly >This probably mean the server terminated abnormally before or while > processing the request. > > This time a error message shows up in the PG log file: "FATAL: could not > reattach to shared memory (key=5432001, addr=011D): Invalid argument" > > As I can tell, the PG log file doesn't tell the whole story. The version of > PG is 8.0. > > I am going to go out on a limb here and say you are running the server on Windows correct? Before we go any further the 8.0.x series is past end of life, especially for the Windows version. The problem you report above FATAL:... is a known Windows version problem. It was fixed in later versions, looks like from 8.2.18 on. I do not run Postgres on Windows, if that indeed is where your server is running, so I am at the limit of what I can help you with. -- Adrian Klaver adrian.kla...@gmail.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] Streaming Rep 101 questions
On 02/02/2011 07:52 AM, Gauthier, Dave wrote: Hi: I'm going to be experimenting with streaming replication using v9.0.1. Here are a few questions I have at the onset... 1) Is it possible to replicate one database out of many that exist in the DB instance? Or do you have to replicate them all? It replicates the cluster. 2) Is replication transaction based? That is to say, does the act of replicating a transaction wait until the commit on the server side? It is shipping WAL records, so when they are recorded they ship, assuming streaming replication. 3) I will be replicating to a DB server 2 timezones away initially, but perhaps from the US-> India at some future point. Is the PG replication solution meant to do something like this (given sufficient bandwidth of course)? Not sure. 4) The slave DB instance will also be v9.0.1 on linux, but it wil be a virtual machine. Do you see any problem with that? FYI 9.0.3 was just released. I have tried it on an EC2 instance as a test and did not see any problems. 5) Is there a step-by-step "how to" document for this? http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial Thanks in Advance ! -- Adrian Klaver adrian.kla...@gmail.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] pg_restore: implied data-only restore\n
On Friday, February 04, 2011 4:35:22 am Andreas Laggner wrote: > Hi list, > > i did a dump (one table) > pg_dump -t tempo.lucas_p1000 -Fc -o -h 134.110.37.20 -p 5432 -U andi -W > gis > /disk2/samba/exportdb/postgres/lucas_p1000_test.out > > and when a want to restore the table > pg_restore -d gis -t tempo.lucas_p1000 -Fc -v -h 134.110.37.20 -p 5432 > -U andi -W /disk2/samba/exportdb/postgres/lucas_p1000_test.out > > pg_restore shows this error: > pg_restore: implied data-only restore\n > > what is wrong? Did it restore the data? According to the source pg_backup_archiver.c: "Work out if we have an implied data-only restore. This can happen if the dump was data only or if the user has used a toc list to exclude all of the schema data. All we do is look for schema entries - if none are found then we set the dataOnly flag." Are you sure you are picking up the right lucas_p100_test.out file? Maybe there is a data only version lying around. One way to test is have pg_restore restore to a file instead of database: pg_restore -f tempo.sql -t tempo.lucas_p1000 /disk2/samba/exportdb/postgres/lucas_p1000_test.out > > thanks for helpAndreas -- Adrian Klaver adrian.kla...@gmail.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] Looking for Suggestion on Learning
On Saturday, February 05, 2011 9:30:13 am ray wrote: > I have built a few databases with MS Access and I would like to learn > how to use pgsql. I have found some examples but they have been too > complex to follow or to abstract with no specific details. > > I would like to find a simple example that would take me from an open > source design tool to a simple method to implement the design. > > I would like to find a simple guide, tutorial or example and will > appreciate any help. > > ray It will be difficult to find a simple drop in replacement for what you had with Access. The closest thing I can think of is OpenOffice/LibreOffice Base (http://help.libreoffice.org/Common/Database_1) and that is not as well integrated. Most Open Source development tend to use chains of tools, admin/creation --> driver/middle layer --> GUI design, with each aspect handled by a different program. I tend to handle admin/creation with text files run through psql. I work with Python so my database driver is psycopg2. This in turn gets used by a framework. For desktop apps I use Dabo (http://dabodev.com/). Since the final output is determined by mix and match it is hard to find a 1-2-3 tutorial. My suggestion is to make a list of your needs and work back from there: 1) What OS(s) do I want to deploy on? 2) What programming language(s) do I want to work with? 3) Where do I want to deploy, desktop/Web? 4) What do I want to build, simple SOHO apps enterprise apps? With answers to these questions it would be possible to narrow the field a bit. Unfortunately, it is one of those good news/bad news situations. Good news, Open Source is about a variety of choices. Bad news, Open Source is about a variety of choices. -- Adrian Klaver adrian.kla...@gmail.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] Looking for Suggestion on Learning
On Saturday, February 05, 2011 6:22:47 pm ray joseph wrote: > > Thank you for the clarifications. I would like to address the guiding > questions you presented: > > 1) What OS(s) do I want to deploy on? Windows, right now XP. > 2) What programming language(s) do I want to work with? Python. > 3) Where do I want to deploy, desktop/Web? Desktop at first. > 4) What do I want to build, simple SOHO apps enterprise apps? SOHO > at first. > > My responses represent what I expect a learning path to take. Although I > will probably not be programming for web or enterprise, what I build may be > a prototype for such. I am not looking for the tools to build the big > apps, I just want to learn the basics, or more important right now, the > simple. Since you are working with Python, another suggestion: http://sqlkit.argolinux.org/sqlkit/tour.html# This has only recently shown up and I myself am only at the reading the docs stage, but it looks interesting. > > Please help me understand what you mean by " I tend to handle > admin/creation with text files run through psql." psql is the command line Postgres client. I create my database objects(tables,views,functions) as separate text files in a text editor(Jedit in my case). I can then use psql to read the files and create/drop objects. I can also work inside psql to do database chores. Another option that I failed to mention earlier is pgAdmin3 (http://www.pgadmin.org/). It is a GUI admin tool for Postgres. If you are using the Windows installer it is included. > > I looked at Dabo and it looks like it is for developing applications. Is > there a tool for designing a database? Pen and paper:) No really that is how I usually start out, sketching out the design on a legal pad. This gets turned into the text files I mentioned above. I have no real experience with using GUI tools for this so I can offer no suggestions. There have been discussions on this topic on the mailing list, so if you search the mailing list archive you will find suggestions. > > Ray -- Adrian Klaver adrian.kla...@gmail.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] Permission denied error - best way to fix?
On 02/08/2011 10:57 AM, Mike Christensen wrote: Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike pg_dump is an admin function so it will need to run as a role that can touch all requested objects and take action. For this particular case you could GRANT SELECT on that table to the restricted user. The trouble being is that this turns into a game of Whack-a-Mole fairly quickly. This is one of those situations where taken a long view on your permissions scheme will pay dividends. -- Adrian Klaver adrian.kla...@gmail.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] Permission denied error - best way to fix?
On Tuesday, February 08, 2011 2:44:51 pm Mike Christensen wrote: > >> Here's the error: > >> > >> pg_dump: SQL command failed > >> pg_dump: Error message from server: ERROR: permission denied for > >> relation pantryitems > >> pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE > >> MODE > >> > >> Does the user need to be a superuser, or is there some way to GRANT > >> this permission (if possible, I don't want this user to be able to > >> modify the schema).. > >> > >> Mike > > > > pg_dump is an admin function so it will need to run as a role that can > > touch all requested objects and take action. For this particular case > > you could GRANT SELECT on that table to the restricted user. The trouble > > being is that this turns into a game of Whack-a-Mole fairly quickly. > > This is one of those situations where taken a long view on your > > permissions scheme will pay dividends. > > Hmm interesting, I wonder if this error is happening because pg_dump > is being run from another program (RubyMine) and the process doesn't > have whatever access it needs. Two possibilities, it is being run as the RubyMine user or as the user that is specified in the connection information RubyMine is using to pull and push data to the database. -- Adrian Klaver adrian.kla...@gmail.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] Possible Bug
On Wednesday, February 09, 2011 1:51:38 am Kaloyan Iliev Iliev wrote:
> Hi,
> I think I found something strange in PostgreSQL behavior. Here is an
> example:
>
>
> testdb=# CREATE TABLE test1 (test2 text, test3 text);
> CREATE TABLE
> testdb=# SELECT A.name FROM test1 A;
> name
> --
> (0 rows)
>
> testdb=# INSERT INTO test1 (test2,test3) VALUES ('1','2');
> INSERT 0 1
> testdb=# INSERT INTO test1 (test2,test3) VALUES ('3','4');
> INSERT 0 1
> testdb=# SELECT A.name FROM test1 A;
> name
> ---
> (1,2)
> (3,4)
> (2 rows)
>
> customer.20080408=# SELECT name FROM test1;
> ERROR: column "name" does not exist
> LINE 1: SELECT name FROM test1;
>
>
> testdb=# SELECT version();
>
> version
> ---
> --- PostgreSQL 8.4.5 on
> amd64-portbld-freebsd8.1, compiled by GCC cc (GCC) 4.2.1 20070719
> [FreeBSD], 64-bit
> (1 row)
>
> Obviously there is no column with name "name", but the SELECT query
> (ONLY WHEN I USE ALIASES) returns result for it.
> And if I have a column with name "name_en" and by mistake write it
> "name", instead of error I receive strange data (ARRAY from all
> columns), that I don't expect.
>
> Could you tell me if this is a bug or some feature I haven't heard of.
Depending on your point of view both. For a good explanation see:
http://www.depesz.com/index.php/2010/11/08/waiting-for-9-1-removed-autocast-
footgun/
As detailed above this feature will go away in 9.1.
>
> Best regards,
> Kaloyan Iliev
--
Adrian Klaver
adrian.kla...@gmail.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] Backup/Restore Needed for Upgrade from 9.0beta4?
On Tuesday, February 08, 2011 10:41:15 am Lee Hughes wrote: > From section 15.4 of the manual: > > "If you are upgrading from PostgreSQL "9.0.x", the new version can use > your current data files so you should skip the backup and restore steps" > > Is 9.0beta4 considered a 9.0.x version, or do I need to backup/restore > when upgrading from that version? > > Thanks much- > > Lee This announcement for the Postgres 9.0rc1 indicated a dump and restore for migration from the betas. I take this to mean yes to dump/restore for what you are doing. http://www.postgresql.org/about/news.1230 -- Adrian Klaver adrian.kla...@gmail.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] Trigger problem, record "new" is not assigned yet
On Thursday, February 10, 2011 6:29:58 am A B wrote: > Hello. > > I'm probably doing some very basic error here, but I get > > ERROR: record "new" is not assigned yet > The tuple structure of a not-yet-assigned record is indeterminate. > > when I try this small example > > create table foo(x int); > > create or replace function trigger_foo() returns trigger language plpgsql > as $$ declare v_i integer; > begin > select count(1) into v_i from foo; > if new.x >18 then > raise exception 'error'; > else > return null; > end if; > end; $$; > > CREATE TRIGGER trigger_foo AFTER INSERT ON foo for each row EXECUTE > PROCEDURE trigger_foo(); > > insert into foo (x) values (15); > > > And the table foo only have 14 lines right now. > > Can someone help me spot the error? :-) The above works here, Postgres 9.0.3. Was that the complete error message? Is there more than one foo across the schemas? -- Adrian Klaver adrian.kla...@gmail.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] Schema Archive cant find table
On Friday, February 18, 2011 7:40:59 am trex005 wrote: > I am trying to archive a schema, however there is a certain table that > gets skipped with pd_dump What does the log file show when you do the dump? Any output to the screen when the dump is being run? If not maybe run with -v switch to get verbose output? > > The table's name is 'servers'. I checked to see if it is a reserved > word, and it does not appear to be... > > Thinking I was missing something I tried using -t servers, and I get : > pg_dump: No matching tables were found > > Any help? > > Second question -- Adrian Klaver adrian.kla...@gmail.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] Finding Errors in .csv Input Data
On Tuesday, February 22, 2011 5:10:34 pm Rich Shepard wrote: >I'm sure many of you have solved this problem in the past and can offer > solutions that will work for me. The context is a 73-column postgres table > of data that was originally in an Access .mdb file. A colleague loaded the > file into Access and wrote a .csv file for me to use since we have nothing > Microsoft here. There are 110,752 rows in the file/table. After a lot of > cleaning with emacs and sed, the copy command accepted all but 80 rows of > data. Now I need to figure out why postgres reports them as having too many > columns. > >Starting to work with a single row, I first compared by cleaned row to > the raw .csv from the Access output. They match column-by-column. Then I > copied the schema to a text file and started comparing the .csv data > column-by-column. While this looks OK to me, postgres doesn't like it. For > example, I get this error message: > > nevada=# \copy water_well from 'one.csv' with delimiter '|' null '' CSV; > ERROR: value too long for type character(1) > CONTEXT: COPY water_well, line 1, column gravel_packed: "106" > >Yet, the column comparison for gravel_packed and surrounding attributes > does not show this: > > lot_no TEXT,| > block_no TEXT, | > well_finish_date DATE, 11/15/1948| > date_cmplt_acc CHAR(1), D| > gravel_packed CHAR(1), | > depth_seal INTEGER, | > depth_drilled INTEGER, 106| > depth_bedrock INTEGER, | > aquifer_desc TEXT, | > >Notice the NULL value for gravel_packed, while the "106" is for > depth_drilled, two columns later. > >I'm stymied and wonder if there's a tool I can use to fix these 80 rows > so the copy command will accept them. > > Rich We are going to need to see at least a sample of the actual data in one.csv that is causing the problem. You have an off by two error as you suggest, but that could actually have happened earlier in the row. For instance the well_finish_date would insert into lot_no because lot_no is TEXT and the date value at this point is just text. Same with date_cmplt_acc and block_no. -- Adrian Klaver adrian.kla...@gmail.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] Finding Errors in .csv Input Data
On Wednesday, February 23, 2011 6:11:16 am Rich Shepard wrote: > On Tue, 22 Feb 2011, David Johnston wrote: > > The data and table structure provided do not seem to correlate. > > David, > >That's the problem. However, they should match since they came from the > same .mdb file. > > > Regardless, if you changed the delimiter to "|" from "," it is possible > > that you converted an embedded "," in one of the textual fields into a > > "|" when you should not have. > >This is why I worked out a processing flow that avoided this problem in > _almost_ every case. The 80 remaining rows do not appear to have this > problem. At least, it's not immediately obvious they do which is why I want > to learn how to identify where (and why) the data get out of sync with the > schema. > > > I suggest opening up a testing file (one with the 80 malformed records > > and 10 to 20 good/control records) in an Excel or equivalent spreadsheet > > and import/text-to-columns using the "|" delimiter. You will be able to > > quickly see rows with extra columns and specifically where those extras > > are originating. Then you can decide on how to fix the problem. I > > would suggest manually changing each incorrect "|" into a "," as a form > > of self-punishment for improper data conversion - but whatever works for > > you. > >Each of the 80 rows have extra columns, at least one. > >Here are the same two rows from the original .csv export and the > transformation: > > 68670,724,"",0,,11/27/1948,"D","N","N","","H","C","32031","087","N18","18N" > ,"E20","20E","07","","","","MD",39.44,119.77,"NV003","M","KAIPER, R > L","","","","SIERRA > MANOR","","",11/15/1948,"D","",,106,,"",106,6.62,0,60,102,1,12.00,,30.00,, > ,"B","G","G","AIR COMPRESSOR TESTED 30 GPM ALSO","","3","MEL MEYER","RT 1 > BOX > 10RENO,",,3,"NV003","JSWINGHOLM",1/16/2003,"",,"F",11/11/1948,,,261013.36, > 4369139.23 > > 68670|724||0||11/27/1948|D|N|N||H|C|32031|087|N18|18N|E20|20E|07MD|39.4 > 4|119.77|NV003|M|KAIPER, R L|||SIERRA > MANOR||11/15/1948|D|||106|||106|6.62|0|60|102|1|12.00||30.00|||B|G|G|AIR > COMPRESSOR TESTED 30 GPM ALSO||3|MEL MEYER|RT 1 BOX 10 > RENO|||3|NV003|JSWINGHOLM|1/16/2003|||F|11/11/1948|||261013.36|4369139.23 > >When I compared the two I saw no difference. Why the latter does not > want to fit the schema I don't know. > >I can send the schema again and the whole 80-row file to anyone willing > to hemp me find the problems. > > Rich As a quick test try quoting KAIPER, R L -->"KAIPER, R L". If that does not work you can send me the 80 line csv file and I will see if anything stands out. -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Thursday, February 24, 2011 1:11:44 pm Aleksey Tsalolikhin wrote: > On Thu, Feb 24, 2011 at 11:46 AM, John R Pierce wrote: > > On 02/24/11 11:02 AM, Aleksey Tsalolikhin wrote: > >> How do I check the fillfactor on the table, please? > > > > its in the field reloptions in pg_class. so... > > > >select reloptions from pg_class where relname='tablename'; > > Thanks, John! > > autovacuum_enabled=true is the only option set on this table > on both the source database and the target. > > >> How do I pg_restore it so that it is 50 GB in size? Is it a setting > >> to pg_dump or to pg_restore? > > > > are you truncating the table before restoring, or is this a restore into > > a new database, or what? > > I've tried both. Slony truncates the table before copying it over, and > I've tryind pg_restore'ing it into a new database. In both cases, the 50 > GB table arrives as a 100 GB table. > > Aleksey What is the schema for this table and associated indexes? Or if that is not available what is in the table? Is the data the same in both for the problem table? I am somewhat suspicious that the second db table is twice the size of the first. Almost like the data is being duplicated. >From above what is the size of the pg_dump for that table only when you dump from the original table versus from the table in the second db? -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Thursday, February 24, 2011 3:34:02 pm Aleksey Tsalolikhin wrote: > Hi. We're running Postgres 8.4.4 everywhere. > > I already have a pg_dump -Fc of the big table from the source, now > I am running a pg_dump -Fc on the recipient, to see if the size is > different. I thought you already had a pg_dump file that you where restoring to the second db? > > Then I will run a pg_dump as text, so I can diff the two files if they are > different in size. You don't need to do that if the pg_dump was done using -Fc. You can use pg_restore to dump a table to a file instead of a database. When it does that the file will contain a plain text copy. Something like: pg_restore -a -t really_big_table -f really_big_table_data.sql Where -a is data only > > Thanks!! > Aleksey -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Thursday, February 24, 2011 3:48:35 pm Adrian Klaver wrote: > On Thursday, February 24, 2011 3:34:02 pm Aleksey Tsalolikhin wrote: > > Hi. We're running Postgres 8.4.4 everywhere. > > > > I already have a pg_dump -Fc of the big table from the source, now > > I am running a pg_dump -Fc on the recipient, to see if the size is > > different. > > I thought you already had a pg_dump file that you where restoring to the > second db? > > > Then I will run a pg_dump as text, so I can diff the two files if they > > are different in size. > > You don't need to do that if the pg_dump was done using -Fc. You can use > pg_restore to dump a table to a file instead of a database. When it does Oops typo should be restore > that the file will contain a plain text copy. Something like: > > pg_restore -a -t really_big_table -f really_big_table_data.sql > > Where -a is data only > > > Thanks!! > > Aleksey -- Adrian Klaver adrian.kla...@gmail.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] select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI')
On Friday, February 25, 2011 6:41:54 am Reid Thompson wrote:
> On 02/25/2011 09:30 AM, akp geek wrote:
> > Hi all -
> >
> > I have recently upgraded from 8.3 to 9.0.2.
> >
> > when I run the following sql in 9.0.2 "select
> > to_timestamp('02/26/2011 14:50', 'MM/DD/ HH24MI') " I am getting the
> > following error and the sql runs fine in older version
> >
> > Is it my installation issue or library missing?
> >
> >
> > ERROR: invalid value ":5" for "MI"
> > DETAIL: Value must be an integer.
> >
> > ** Error **
> >
> > ERROR: invalid value ":5" for "MI"
> > SQL state: 22007
> > Detail: Value must be an integer.
> >
> >
> >
> > Thanks for the help
> > Regards
>
> military time doesn't have the ':' in the HHMM.
> look closely at your 'working' result, does it look like this?
>
>
> reporting=# select to_timestamp('02/26/2011 14:50', 'MM/DD/ HH24MI');
>to_timestamp
>
> 2011-02-26 14:00:00-05
> (1 row)
>
> reporting=# select to_timestamp('02/26/2011 1450', 'MM/DD/ HH24MI');
>to_timestamp
>
> 2011-02-26 14:50:00-05
> (1 row)
>
> Note the invalid time for the first result.
To confirm above I ran your query on 8.3.
Your original version:
test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/ HH24MI');
to_timestamp
2011-02-26 14:00:00-08
(1 row)
Corrected for the ':':
test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/ HH24:MI');
to_timestamp
2011-02-26 14:50:00-08
(1 row)
On 9.0.3 corrected for the ':':
test(5432)aklaver=>select to_timestamp('02/26/2011 14:50', 'MM/DD/
HH24:MI');
to_timestamp
2011-02-26 14:50:00-08
(1 row)
Seems 9.0+ is stricter on its parsing.
--
Adrian Klaver
adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Sunday, February 27, 2011 12:49:48 am Aleksey Tsalolikhin wrote: > so looks like it's something low-level, something about how the data is > stored. > > i've installed the "pageinspect" contrib module as Tomas suggested but > I don't know what to do with it or what to look at. I looked at the > manual for it but it's totally over my head right now. > > What sections of the manual should I read to be able to use this > module? (there are 2167 pages in the whole Postgres 8.4 manual and I > don't have time to study the whole thing. :( I just need to study > enough to understand how to use pageinspect.) > > (I'm not a Postgres DBA but I am having to become one to support our > database.) > > Thanks again for the help. I'll be at SCALE 9x tomorrow helping Joe > Conway and Bruce M and Richard B and company get the word out about > Postgres. Well it looks like the problem is not with the raw data. So far you have been using pg_total_relation_size to look at the table. It might be time to look at the individual components by using the other pg_* functions: http://www.postgresql.org/docs/9.0/interactive/functions-admin.html pg_table_size(regclass) pg_indexes_size(regclass) pg_relation_size(relation regclass, fork text) In other words try to narrow down where the bloat is occurring. > > Best, > Aleksey -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Hot Standby - ERROR: canceling statement due to conflict with recovery
On Sunday, February 27, 2011 11:57:35 am Sean Laurent wrote: > On Sun, Feb 27, 2011 at 1:04 PM, Jens Wilke wrote: > > On Sonntag, 27. Februar 2011, Sean Laurent wrote: > > > Unfortunately, most queries against the hot standby fail. Worse > > > > > yet, pg_dump fails: > > ... > > > > > I'm not entirely certain I understand why I'm seeing this. Nor do > > > I understand how to fix or work around this. Any advice or > > > suggestions would be greatly appreciated. > > > > Long running queries on the standby are a bit tricky, because they > > might need to see row versions that are already removed on the > > master. > > It's well documented: > > http://www.postgresql.org/docs/9.0/static/hot-standby.html > > Right. I read all of that. I guess I just assumed it was possible to create > a snapshot on the standby so that a longer running on the standby could > complete. In particular, I was really hoping to run database dumps against > the standby, not the master. > > Thanks. > > Sean From the above link: "The most common reason for conflict between standby queries and WAL replay is "early cleanup". Normally, PostgreSQL allows cleanup of old row versions when there are no transactions that need to see them to ensure correct visibility of data according to MVCC rules. However, this rule can only be applied for transactions executing on the master. So it is possible that cleanup on the master will remove row versions that are still visible to a transaction on the standby. " Below that it goes into some possible solutions, the easiest of which to test would seem to be: "Another option is to increase vacuum_defer_cleanup_age on the primary server, so that dead rows will not be cleaned up as quickly as they normally would be. This will allow more time for queries to execute before they are cancelled on the standby, without having to set a high max_standby_streaming_delay. However it is difficult to guarantee any specific execution-time window with this approach, since vacuum_defer_cleanup_age is measured in transactions executed on the primary server. " -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Permission denied when inserting
On Monday, February 28, 2011 6:37:51 am Borek Lupomesky wrote: > Hello, > > I have a database app that worked fine until we reinstalled the > server with the related DB dump and restore. Most of the stuff works > fine after the reinstall, but one particular insert gives very cryptic > (for me) message: > > spam=> INSERT INTO permout ( site, cp, owner, descr, creat_who ) VALUES > ( 'vin', '3035.1', 'borelupo', 'test', 'borelupo' ); > ERROR: permission denied for relation out2cp > CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."out2cp" x WHERE > "site"::pg_catalog.text OPERATOR(pg_catalog.=) $1::pg_catalog.text AND > "cp"::pg_catalog.text OPERATOR(pg_catalog.=) $2::pg_catalog.text FOR > SHARE OF x" > > Note, that I am inserting into table "permout", but the message is > about permission for "out2cp". Any idea what went wrong? When I was > doing the dump I forgot to dump all the permissions as well so I had to > restore them manually and possibly something is not right somewhere, but > the error message given is of no help to me. What does the "CONTEXT" > message actually mean? > More information is below (I'm logged in as user 'borelupo'). Just to be clear where you borelupo when you ran the above statement? Also what do: SELECT current_user; SELECT session_user; show when you are logged in? -- Adrian Klaver adrian.kla...@gmail.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] Permission denied when inserting
On Monday, February 28, 2011 8:02:53 am Tom Lane wrote:
> Vick Khera writes:
> > On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky wrote:
> >>"permout_site_fkey" FOREIGN KEY (site, cp) REFERENCES out2cp(site,
> >> cp) ON DELETE CASCADE
> >
> > you don't have permissions to verify the FK constraint. the query you
> > see in the error is exactly this test.
>
> Right. But actually, that query will be run with the permissions of the
> owner of the table, so it's that user (not necessarily the one doing the
> INSERT) who lacks permissions.
>
> It sounds like the dump-and-restore process was pretty incomplete.
> How was it done exactly? Not with pg_dumpall, I'm guessing.
>
> regards, tom lane
The OP listed the permissions for the tables:
pam=> \z permout
Access privileges for
database "spam"
Schema | Name | Type |
Access privileges
+-+---+---
public | permout | table |
{borelupo=arwdxt/borelupo,swcoll=r/borelupo,swcgi=arwdx/borelupo,spamdump=r/borelupo,facility=r/borelupo}
(1 row)
spam=> \z out2cp
Access privileges for
database "spam"
Schema | Name | Type |
Access privileges
++---+---
public | out2cp | table |
{swcoll=r/petrcech,swcgi=r/petrcech,spamdump=r/petrcech,facility=r/petrcech,borelupo=arwdxt/petrcech}
(1 row)
Looks like borelupo is owner of permout and has permissions on out2cp.
--
Adrian Klaver
adrian.kla...@gmail.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] Permission denied when inserting
On Monday, February 28, 2011 8:17:07 am Tom Lane wrote:
> Adrian Klaver writes:
> > On Monday, February 28, 2011 8:02:53 am Tom Lane wrote:
> >> Right. But actually, that query will be run with the permissions of the
> >> owner of the table, so it's that user (not necessarily the one doing the
> >> INSERT) who lacks permissions.
> >
> > The OP listed the permissions for the tables:
> >
> > spam=> \z out2cp
> >
> >Access privileges for
> >
> > database "spam"
> >
> > Schema | Name | Type |
> >
> > Access privileges
> > ++---+---
> >
> >
> > public | out2cp | table |
> >
> > {swcoll=r/petrcech,swcgi=r/petrcech,spamdump=r/petrcech,facility=r/petrce
> > ch,borelupo=arwdxt/petrcech} (1 row)
> >
> > Looks like borelupo is owner of permout and has permissions on out2cp.
>
> But what matters is the permissions of the owner of out2cp, which looks
> from this to be petrcech. And I don't see that he's granted himself
> any permissions.
>
> regards, tom lane
I understand, yet I don't:) Seems I have a misconception of the the FOREIGN KEY
process. The error was on the query below, which I am taking is the query you
refer to above. To me it looks like a look up from the referencing(permout)
table to the referenced(out2cp) one. The part I am not clear on is why that
runs
with the permissions of the referenced table not the referencing table?
ERROR: permission denied for relation out2cp
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."out2cp" x WHERE
"site"::pg_catalog.text OPERATOR(pg_catalog.=) $1::pg_catalog.text AND
"cp"::pg_catalog.text OPERATOR(pg_catalog.=) $2::pg_catalog.text FOR
SHARE OF x"
--
Adrian Klaver
adrian.kla...@gmail.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] Permission denied when inserting
On Monday, February 28, 2011 9:15:27 am Tom Lane wrote: > Adrian Klaver writes: > > I understand, yet I don't:) Seems I have a misconception of the the > > FOREIGN KEY process. The error was on the query below, which I am > > taking is the query you refer to above. To me it looks like a look up > > from the referencing(permout) table to the referenced(out2cp) one. The > > part I am not clear on is why that runs with the permissions of the > > referenced table not the referencing table? > > Because we force it that way so that you don't have to splatter > permissions all around to support a foreign key check. Generally > the internal FK check queries will only fail if the owner of a table > revokes his own permissions, which isn't too usual. > > regards, tom lane Had not thought about it from that angle. Thanks. -- Adrian Klaver adrian.kla...@gmail.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] Lock ACCESS EXCLUSIVE and Select question !
On Monday, February 28, 2011 2:39:07 pm Alan Acosta wrote: > But i have a new problem there, i have no rows for update, i create new > rows when the seats are sold, cause rarely the database knows the capacity > of the bus, number of seats, even some bus doesn't have seat number 4 for > example So how does the customer know what seat to choose? Or to put it another way, when the INSERT is done where does the seat number come from? > > :p. So i cannot SELECT FOR UPDATE no existent rows, but i still need to > :lock > > in some way in order to check if seat number X was already sold or is free > ! > > Alan Acosta -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Monday, February 28, 2011 9:51:10 pm Aleksey Tsalolikhin wrote: > On Sun, Feb 27, 2011 at 2:52 AM, Alban Hertroys > wrote: > Thank you for your kind replies. > > > I noticed in your table definition that you seem to store timestamps in > > text-fields. Restoring those from text-fields shouldn't make any > > difference, but perhaps your locales are set up differently between the > > machines and cause some type of conversion to take place? > > OK, Alban, I'm game. How would I check how locales are set up? > > Adrian, I found pg_indexes_size() is only in 9 (I have 8.4) but I got > the same information from a query based on > http://www.issociate.de/board/post/478501/How_much_space_do_database_object > s_take_up_in_data_files.html Sorry about that, I was not paying attention. FYI 8.4 does have pg_relation_size() which can be applied against individual indexes. > > > Here is what I see: > > > > nspname | relname | tablesize > > | indexsize | toastsize | toastindexsize > > +--++-- > --++ public | big >| 744 MB > > | 737 MB | 48 GB | 278 MB > > public | big | 503 MB > > | 387 MB | 99 GB | 278 MB > > Check out that toastsize delta. What makes up TOAST? How can I > compare the two TOAST tables in detail? TOAST is best explained here: http://www.postgresql.org/docs/8.4/interactive/storage-toast.html Looks like the TOAST compression is not working on the second machine. Not sure how that could come to be. Further investigation underway:) > > Thanks, > Aleksey -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Monday, February 28, 2011 9:51:10 pm Aleksey Tsalolikhin wrote: > > Here is what I see: > > > > nspname | relname | tablesize > > | indexsize | toastsize | toastindexsize > > +--++-- > --++ public | big >| 744 MB > > | 737 MB | 48 GB | 278 MB > > public | big | 503 MB > > | 387 MB | 99 GB | 278 MB > > Check out that toastsize delta. What makes up TOAST? How can I > compare the two TOAST tables in detail? > The compression/no compression thing tickled a memory. Run \d+ against the table in question. It should show a storage column with values for each field. Are any of those set to EXTERNAL instead of the default EXTENDED? -- Adrian Klaver adrian.kla...@gmail.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] Why count(*) doest use index?
On 03/03/2011 05:29 AM, obamaba...@e1.ru wrote: I use pgsql 9.0.3 and I know that postgresql tries to use the fields in indexes instead of the original table if it possible But when I run SELECT COUNT(id) FROM tab or SELECT COUNT(*) FROM tab where there "id" is PRIMARY KEY and there are other indexes there I get execution plan that doesnt use any indexes, but sequentab scanning the original table. "Aggregate (cost=38685.98..38685.99 rows=1 width=0)" " -> Seq Scan on tab (cost=0.00..36372.38 rows=925438 width=0)" Why is it so? See here: http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F --- Paul -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Thursday, March 03, 2011 6:15:50 pm Aleksey Tsalolikhin wrote: > On Tue, Mar 1, 2011 at 7:24 AM, Tom Lane wrote: > > Adrian Klaver writes: > >> Looks like the TOAST compression is not working on the second machine. > >> Not sure how that could come to be. Further investigation underway:) > > > > Somebody carelessly messed with the per-column SET STORAGE settings, > > perhaps? Compare pg_attribute.attstorage settings ... > > Thank you. I compared the STORAGE settings and I have "extended" on > both databases, > no "external". > > Any other ideas? Weird. The pgstattuple data shows that the tables are essentially the same, the only difference being the dead tuples, as expected, on the production table. The TOAST size information shows approximately a doubling in size of the TOASTed data on the new machine. By all accounts compression or the lack thereof would be the culprit. At this point I am at a loss for another explanation. One more request for information. What is the data being stored in the table? > > Yours truly, > -at -- Adrian Klaver adrian.kla...@gmail.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] script errors or PEBKAC?
On 03/04/2011 07:48 AM, Thufir Hawat wrote: I cannot get the script here: https://docs.google.com/leaf?id=0B5hKxkS1VyAxOGMzZjY4ZjktZjZkOS00Zjc3LWExYmEtYTU3ZThjYzZiMjk3&hl=en to run correctly. Looking at the output, many, many, errors, it seems to assume tables exist which don't. Is that correct? Actually no. The tables do exist but they have dependencies: ERROR: cannot drop table izposoja because other objects depend on it DETAIL: constraint fk_knjiga_i_relations_izposoja on table knjiga_izposoja depends on table izposoja HINT: Use DROP ... CASCADE to drop the dependent objects too. The script is trying to DROP the table but cannot as CASCADE was not included in the DROP statement. This is repeated for other tables also. This accounts for some of the errors. The rest seem to follow from this. The tables are not dropped but then data is put into them that conflicts the pre-existing data. The script is supposed to create a database, but it seems to assume that the schema is already there and configured? If it is supposed to create a database it is doing it on one that exists already, which this script is unable to handle. Without seeing the actual script it hard to say exactly why. Or, maybe I'm not running it correctly? Yes:) thanks, Thufir -- Adrian Klaver adrian.kla...@gmail.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] script errors or PEBKAC?
On 03/04/2011 07:48 AM, Thufir Hawat wrote: I cannot get the script here: https://docs.google.com/leaf?id=0B5hKxkS1VyAxOGMzZjY4ZjktZjZkOS00Zjc3LWExYmEtYTU3ZThjYzZiMjk3&hl=en to run correctly. Looking at the output, many, many, errors, it seems to assume tables exist which don't. Is that correct? The script is supposed to create a database, but it seems to assume that the schema is already there and configured? Or, maybe I'm not running it correctly? thanks, Thufir Did not realize actual script was at bottom of text file. I see nothing in the script that creates a database, so the script assumes you have created an empty database to run against. You seem to be running it a populated database. -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote: > On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver wrote: > > Weird. The pgstattuple data shows that the tables are essentially the > > same, the only difference being the dead tuples, as expected, on the > > production table. The TOAST size information shows approximately a > > doubling in size of the TOASTed data on the new machine. By all accounts > > compression or the lack thereof would be the culprit. At this point I am > > at a loss for another explanation. > > An apt summary, thank you. > > > One more request for information. What is the data being stored in the > > table? > > For the main part, it's an XML file, we store it in the third field. > Our XML files are > 13KB - 48 KB in length. > And you are positive that field has not had its storage changed to something other than EXTENDED? > > I ran char_length() on each XML file, the total is 38 GB, which is pretty > close to the 40 GB size of the pg_dump of the table in text format. > > From my observation, when the table was under 30 GB in size, > TOAST compression worked fine; when it got above 35 GB in size, > now TOAST compression is not working. More questions:) How do you know that? I thought the size problem only came to light when you tried to set up another server. Was there some sort of monitoring going on previous to setting up the new server? Anything else happen around that time? > > Yours truly, > -at -- Adrian Klaver adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Friday, March 04, 2011 5:11:04 pm Aleksey Tsalolikhin wrote: > On Fri, Mar 4, 2011 at 4:45 PM, Adrian Klaver wrote: > > On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote: > >> On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver wrote: > >> >What is the data being stored in the table? > >> > >> For the main part, it's an XML file, we store it in the third field. > >> Our XML files are 13KB - 48 KB in length. > > > > And you are positive that field has not had its storage changed to > > something other than EXTENDED? > > Quite. I just double checked on both servers at it is EXTENDED for > everything but > the timestamp (5th field) which is PLAIN (on both servers). > > >> From my observation, when the table was under 30 GB in size, > >> TOAST compression worked fine; when it got above 35 GB in size, > >> now TOAST compression is not working. > > > > More questions:) > > How do you know that? > > I thought the size problem only came to light when you tried to set up > > another server. > > Was there some sort of monitoring going on previous to setting up the new > > server? > > Anything else happen around that time? > > OK, I have to withdraw my "observation". Let me refine it (which I am > afraid makes > it less useful): I've done pg_dump/restore of the database earlier, when > it was smaller (< 30 GB) and did not notice such a doubling in size; I > don't think it occurred. Certainly the last time we moved the database > from the DR back to primary site, it did not double in size from 1 GB (it > was around 1 GB then). > > Here is what I did see: we've had Slony replication running for a > while (over half > a year) from Primary to DR; and file system utilization on Primary and > DR was about > the same. (around 75%). > > Also, I had done some pg_dump's / pg_restore's from DR to Dev and Stage, > and the database size was about 1:1. > > But most recently, I shut down replication, and modified the cluster config > (added several tables and sequences; plus some tables were modified so > I wanted to get a clean start on replicating them). I removed the slony > schemas and re-created a Slony replication set -- my filesystem was 75% > full on the master, > and it hit 100% on the slave! So I lost my slave! > > Then I tried pg_dump/pg_restore and noticed the same thing, that one table > doubles in size. > > Last time I did a full Slony re-sync like this was around 30 GB. Now > we're up to > 40-50 GB and hit the 1:2 factor. > > I can't think of anything else happening around this time... > > I'm going to try splitting the pg_dump file (40 GB) in half, and load the > 20 GB file, and see how much space it takes up in the database, I'm > curious if I can replicate the 1:2 swelling with this smaller table. > > Aleksey Going over the saga to date. 1)Slony replication running between production server and DR server for half a year or so. Where the Postgres versions the same between servers? 2) Replication shut down, cluster configuration modified Assuming that the problem table was not one of the ones added correct? 3)In your first email you mentioned upgrading the production server. Was this a version upgrade? Major or minor upgrade? From what to what? Otherwise what was the upgrade? 4)Dump/restore to Dev and Stage seem to be alright. Have either of these been touched by Slony? Have you tried this with the data set you have been having problems with? 5)The restore to DR server is showing size growth of 2x. Is this a fresh instance of Postgres or is it the instance that was under Slony replication previously? -- Adrian Klaver adrian.kla...@gmail.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] School teacher in need of HELP
On Saturday, March 05, 2011 12:33:50 pm hbaile...@nyc.rr.com wrote: > Hello, > I have no IT experience and no funding to hire a technical person or > resources to take a training course. Also my friends who have programming > background have not been reliable. I am willing to spend the necessary > months in trying to learn how to use an SQL program. I just installed > PostgresSQL 9.0. While trying to read through the documentation I > proceeded to chpt. 1.3 Creating a Database, which commands I followed. > Chpt. 1.4 Accessing a Datebase commands are confusing. I tried to enter > the commands on the SQL Shell(psql) from which line 1 reads Server > [localhost], line 2 –Database[postgres]: and I entered postgres, line 3 – > Port[], which number I entered), line 4 - Username[postgres], where I > entered postgres, line 5 Password for the postres: ... which > returned the message FATAL : password authentication failed for user > “postgres”. Press any key to continue. > > Can you help me understand how to start accessing the server database > capabilities and/or mention on online support blog. > > Thanking you in advance, > Hilary First what platform(Windows,Linux,Mac) are you using? How did you install Postgres? My guess is that the database was set with trust authentication, this means you do not have to supply a password. Try the command without a password. -- Adrian Klaver adrian.kla...@gmail.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] Deleted database from pgAdmin
On Sunday, March 06, 2011 4:24:56 pm akp geek wrote: > Hi all - > >wanted to check, if it is possible to recover the database, > if it is deleted using the pgAdmin. I know it is not possible. Just > wondering . We don't have back either :( Just to be sure you actually deleted the database, not just the reference to it in pgAdmin? In other words doing a \l in psql does not show the database. > > Regards -- Adrian Klaver adrian.kla...@gmail.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] PG and dynamic statements in stored procedures/triggers?
On Monday, March 07, 2011 6:32:44 am Durumdara wrote: > Hi! > > In other RDBMS I found a way to make dynamic statements. > I can use variables, or concat the SQL segments, and execute it all. > > :tablename = call CreateTempTable; > > insert into :tablename > drop table :tablename > > or (FireBird like cursor handling): > > sql = "select * from " || :tablename || " where..." > for select :sql > ... > > Can I do same thing in PGSQL too? > > Thanks: >dd http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL- STATEMENTS-EXECUTING-DYN -- Adrian Klaver adrian.kla...@gmail.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] PG and dynamic statements in stored procedures/triggers?
On Monday, March 07, 2011 6:45:11 am Durumdara wrote: > Hi! > > Thanks! > > How do I create "cursor" or "for select" in PGSQL with dynamic way? > > For example > > :tbl = GenTempTableName() > > insert into :tbl... > insert into :tbl... > insert into :tbl... > > for select :part_id from :tbl begin > exec 'select count(*) from subitems where id = ?' using :part_id into > > :sumof > > update :tbl set sumof = :sumof where part_id=:part_id > end; > > Can you show me same example? There are examples in the docs at the link provided. Though I would suggest reading the pl/pgsql documentation from the beginning to get an idea of its structure. > > Thanks: > dd -- Adrian Klaver adrian.kla...@gmail.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] PG and dynamic statements in stored procedures/triggers?
On Monday, March 07, 2011 1:16:11 pm Bill Thoen wrote:
>
> For example, I have a need for a tool that gets an initial record id
> from the user, then it looks up that key and finds the primary keys of
> two other tables related to the firstkey, then it looks those tables up
> and displays the data from each side by side so I can check the
> differences between the records. (Basically, it's a case of data from
> two vendors that carry a common key, and I'm just spot checking). I've
> been using interactive psql, but I thought an app as simple as this is
> in concept wouldn't be so hard to do, but it is if you don't know enough
> of what's in the API like, isn't there a function to enumerate a table's
> attributes?. Or how do you capture the results of a select that calls a
> function in SQL? (e.g.:
> \set myResults
>
> :myResults = SELECT myFunction();
>
> -- this won't fly; nor will this:
> SELECT INTO :myResults myFunction();
A possible solution from here:
http://www.postgresql.org/docs/9.0/interactive/sql-createtableas.html
"
PREPARE recentfilms(date) AS
SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
EXECUTE recentfilms('2002-01-01');
"
>
> Anyway, I'm begining to see that I had some misconceptions about what
> you can do within SQL and what you're better off doing in plpgsql. Or C.
> Read the whole section on variables in the manual. That's very good
> advice. In fact, peruse it. Because if you read it lightly, you'll have
> to to go over it again and again.
>
> But after reading your note, dynamic SQL seems like it might be just
> what I'm looking for too. Didn't realize it was an option, since I see
> it's documented near the end of the manual, and there's only so much
> RTFMing I can do at a sitting, so that's all new territory to me. But if
> it works like you've sketched out here... well I'm going to try it and see.
On Postgres 9.0+ there is also DO
http://www.postgresql.org/docs/9.0/interactive/sql-do.html
--
Adrian Klaver
adrian.kla...@gmail.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] database is bigger after dump/restore - why? (60 GB to 109 GB)
On Monday, March 07, 2011 2:45:00 pm Aleksey Tsalolikhin wrote: > > Experiment 1: > hypothesis: something about how large my table has grown is causing > the TOAST compression to fail on COPY. > test: pg_dump the big table, cut the dump file in half using > "/bin/split", add "\." at the end of the file, and load the top half. > result: database is 50 GB in size. hypothesis proven false. > > Experiment 2: > hypothesis: something about Slony is causing the TOAST compression to > be disabled on COPY. > test: load the 50% dump file from experiment 1 above into our Stage > database, which was never touched by Slony. > result: database is 50 GB in size. hypothesis proven false. Hmmm. Another perfectly good line of reasoning shot down. No further thoughts at this time. May have to sit down with Jack Daniels and have a talk and see if anything shakes loose:) > > > Best, > Aleksey -- Adrian Klaver adrian.kla...@gmail.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] FW: backup using pg_dump postgreSQL 8.3.8
On Wednesday, March 09, 2011 5:58:20 am Sandy Test wrote: > > > > > 2. Is there a way of running a dos script that would run the pg_dump > providing a password when prompted? Using .pgpass http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html > > > > Thanks > > > > Sandy Test > -- Adrian Klaver adrian.kla...@gmail.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] Why length(to_char(1::integer, '9')) = 2 ?
On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote: > > But I am missing something or there is a documentation inaccuracy: > http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO > NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and > zeroes) > > Test: > dmitigr=> select to_char(12,'FM0009'); > to_char > - > 0012 > > dmitigr=> select length(to_char(12,'FM0009')); > length > > 4 > > So, FM suppresses only padding blanks not zeroes... > > Any comments? > test(5432)aklaver=>select to_char(12,''); to_char - 12 test(5432)aklaver=>select to_char(12,'FM'); to_char - 12 It is a little confusing, but you asked for the 0 in your specification so they are not considered padding. Look at the examples in the table listed below to get an idea of what I am talking about. http://www.postgresql.org/docs/9.0/static/functions-formatting.html Table 9-25 -- Adrian Klaver adrian.kla...@gmail.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] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?
On 03/09/2011 08:30 AM, John Edens wrote: Hey guys, I'm trying to get a VB program to make a client connection to my PostgreSQL server running on an Ubuntu 10.10 server. Here's what I've done: Client side - installed and registered the OLE DB .dll's from the PostgreSQL OLE DB Provider project. server side - the configuration described here: https://help.ubuntu.com/10.10/serverguide/C/postgresql.html --- Have added this line to postgresql.conf: listen_addresses = '*, 144.96.80.35, localhost' I would stick with listen_addresses = '*', this covers all the bases and simplifies things for the time being. I've also tried this as simply: listen_addresses = '144.96.80.35, localhost' also, port = 5432 -- I have added the following to pg_hba.conf: # IPv4 local connections: host all all 144.96.80.35 255.255.255.0 md5 local all postgres md5 If I am following, 144.96.80.35 is your server address not the clients. In pg_hba.conf the IPs are those from the clients you want to let in, so you will need to use an IP or mask that matches that of your client. To reduce confusion you might want to do that in the section for remote connections :) -- I've got the following code going in VB: Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Const connString As String = "Provider=PostgreSQL; Server=144.96.80.35; User Id=postgres;Password=postgres;" Dim theOleDBconnection As New OleDb.OleDbConnection theOleDBconnection.ConnectionString = connString theOleDBconnection.Open() End Sub End Class So when the project opens up, it should make the connection and display the form. What happens is that I get an error... OleDbException was unhandled could not connect to server: Connection refused (0x274D/10061) Per the explanation above your client IP does not have access to the server. Is the server running on host "" and accepting TCP/IP connections on port 5432? --- So, obviously I've got something configured incorrectly. I'm troubled that the VB error seems to show a null value for the host. Shouldn't that be the Server value in the connection string? Thanks to all for any help… -- Adrian Klaver adrian.kla...@gmail.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] Why length(to_char(1::integer, '9')) = 2 ?
On 03/09/2011 09:59 AM, Dmitriy Igrishin wrote: 2011/3/9 Adrian Klaver mailto:adrian.kla...@gmail.com>> On Wednesday, March 09, 2011 5:34:41 am Dmitriy Igrishin wrote: > > But I am missing something or there is a documentation inaccuracy: > http://www.postgresql.org/docs/9.0/static/functions-formatting.html#FUNCTIO > NS-FORMATTING-NUMERICMOD-TABLEsays: fill mode (suppress padding blanks and > zeroes) > > Test: > dmitigr=> select to_char(12,'FM0009'); > to_char > - > 0012 > > dmitigr=> select length(to_char(12,'FM0009')); > length > > 4 > > So, FM suppresses only padding blanks not zeroes... > > Any comments? > test(5432)aklaver=>select to_char(12,''); to_char - 12 test(5432)aklaver=>select to_char(12,'FM'); to_char - 12 It is a little confusing, but you asked for the 0 in your specification so they are not considered padding. Look at the examples in the table listed below to get an idea of what I am talking about. http://www.postgresql.org/docs/9.0/static/functions-formatting.html Table 9-25 Yes, I see, thanks! I just talking about phrase "fill mode (suppress padding blanks and zeroes)" in the documentation should be rephrased to "fill mode (suppress padding blanks)". To get technical it means suppress unspecified padding O's. See below for example. Or I misunderstood what is "padding zeroes" without explicitly specification "0" pattern in the format format template... This combination from the example table shows that: to_char(-0.1, 'FM9.99') '-.1' to_char(0.1, '0.9') ' 0.1' The 0 in 0.1 is not strictly needed, so if you use FM it will be suppressed. -- Adrian Klaver adrian.kla...@gmail.com <mailto:adrian.kla...@gmail.com> -- // Dmitriy. -- Adrian Klaver adrian.kla...@gmail.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] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?
On 03/09/2011 09:59 AM, John Edens wrote: If I am following, 144.96.80.35 is your server address not the clients. In pg_hba.conf the IPs are those from the clients you want to let in, so you will need to use an IP or mask that matches that of your client. To reduce confusion you might want to do that in the section for remote connections :) Per the explanation above your client IP does not have access to the server. Okay, I have set the mask as described... I can now use pgAdmin III to access the database on the server from my desktop machine, which I could not previously do. However, the VB code still gives the error previously described. Thanks for the config file help - any ideas about the OLE DB connection problem? What I know about OLE would fit in the navel of a flea:) That being said I offer: http://www.connectionstrings.com/postgre-sql#p53 It would seem your connection string needs different information. -- Adrian Klaver adrian.kla...@gmail.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] pg_restore: [archiver] unsupported version (1.12) in file header
On Wednesday, March 09, 2011 12:19:13 pm akp geek wrote: > I am getting the following error when I try to restore it from a dump. Any > suggestions? At a guess you are trying to go backwards, using a newer dump file with an older pg_restore. In other words doing 9.0 pg_dump and then 8.3 pg_restore. Could you clarify what is the exact procedure you are using? > > Regards -- Adrian Klaver adrian.kla...@gmail.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] Post-Upgrade Question: 9.0.1 -> 9.0.3
On Thursday, March 10, 2011 6:54:18 am Rich Shepard wrote: >After carefully following the instructions here: > <http://www.postgresql.org/docs/9.0/static/install-upgrading.html> > I tested the upgrade from the command line. My question is why I still see > a reference to 9.0.1; > > psql (9.0.1, server 9.0.3) > Type "help" for help. > >One database was created with 9.0.1 but another was created in the 7.x > era. > > Rich Your psql binary is from 9.0.1 your server from 9.0.3. Somewhere you have the old psql in your path and it is being found first. -- Adrian Klaver adrian.kla...@gmail.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] Post-Upgrade Question: 9.0.1 -> 9.0.3
On Thursday, March 10, 2011 8:11:37 am Rich Shepard wrote: > On Thu, 10 Mar 2011, Adrian Klaver wrote: > > Your psql binary is from 9.0.1 your server from 9.0.3. Somewhere you have > > the old psql in your path and it is being found first. > > Adrian, > >Found it. There was an executable /bin/psql in addition to > /usr/local/psql/bin/psql. I've no idea how the former got there during the > installation of 9.0.1. My guess is you did a Postgres client only package install at some point:) > >I removed /bin/psql and made a softlink from the 9.0.3 version to /bin. > > Thanks, > > Rich -- Adrian Klaver adrian.kla...@gmail.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] Upgraded to 9.0.3, No Man Pages
On Thursday, March 10, 2011 11:36:36 am Bruce Momjian wrote: > Rich Shepard wrote: > > On Thu, 10 Mar 2011, Bruce Momjian wrote: > > > I don't think we ever researched how to install manual pages in all the > > > distributions. /share does make sense; not sure why that doesn't > > > happen. > > > > Bruce, > > > >That's where they were installed with 9.0.1 and that's where I > >expected to > > > > find them after upgrading to 9.0.3. No such luck. > > Odd. How did you do the install? Via source? When I went to check I found my source install of 9.0.3 did not install docs. Comparing the GNUMakefiles from an 8.4 and 9.0.3 install shows that the docs have been split out to the targets docs: and install-docs: and are no longer in the targets all: and install: -- Adrian Klaver adrian.kla...@gmail.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] primary key
On Monday, March 14, 2011 12:57:07 am Peter Evens wrote:
> hello,
>
> i have a question about the PRIMARY KEY,
> how can we let it start from for example 1000 instead of 1?
>
> This is our program:
> CREATE TABLE hy3_pack
> (
> hy3_id serial NOT NULL,
> hy3_serie_nummer text NOT NULL,
> hy3_barcode text NOT NULL,
> hy3_type_vulling text NOT NULL,
> hy3_tarra_gewicht text,
> hy3_artikel_id numeric NOT NULL,
> hy3_refill boolean,
> vernietigd boolean,
> opmerking text,
> CONSTRAINT hy3_pack_pkey PRIMARY KEY (hy3_id)
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE hy3_pack OWNER TO postgres;
>
> what must i change in my program?
>
First do something like below to get the name of the sequence associated with
the serial type:
test(5432)aklaver=>\d serial_test
Table "public.serial_test"
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('serial_test_id_seq'::regclass)
fld_1 | text|
Then go here:
http://www.postgresql.org/docs/9.0/interactive/functions-sequence.html
and use one of the setval() functions to set the sequence start to 1000.
Note: This is assuming the table is empty and you will not have any id
collisions.
--
Adrian Klaver
adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 5:09:27 am Alphadion wrote: > Hi, > > I was using Holdem Manager and Postgres database server until a month ago > when it just stopped. Whatever I did didn't help, incl. a teamviewer > session with one of the HM administrators. > > I've already read and done everything about that in FAQ, forums,net etc. > and nothing helps. > > Please help! I am using Windows 7 64, I've also tried with/without > firewalls, opened all local ports... Using Nod32 atm and Spybot that runs > in the background, when ununstalled (both of them) postgres doesn' t work > too Some information required: What version of Postgres? How was Postgres installed? How do you know Postgres is not working? Or is that Holdem is not finding Postgres? Is all of this happening locally or is there a remote component? > > Cheers, > > Dion > -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 8:55:33 am Alphadion wrote: > I tried to install any of the versions but it always gives that message > "Could not connect to the database server, please check port 5432 and > 127.0.0.1" or something like that. I've opened a range of ports on my > router's page, and also opened one in my computer through the firewall. > > Holdem Manager is a tracking software for playing poker online. It stores > all yours and opponents' hands and uses Postgresql for that. It was working > properly until one day when it just stopped, giving a message about > inability to onnect to database server. I tried system restore and > everything possible, including a teamviewer session with one of the Holdem > Manager forums admins. Nothing helped... > > I dont want to reinstall the whole Windows as I'm not sure if it's going to > work. I tried installing HM and postgres on another computer in the same > network and it doesnt work on it too... To get this moving please answer the questions I provided earlier: What version of Postgres? How was Postgres installed? How do you know Postgres is not working? Or is that Holdem is not finding Postgres? Is all of this happening locally or is there a remote component? In addition: Anything happen a month ago when it stopped working? Software upgrade? Computer crash? Etc? > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-coul > d-not-be-installed-tp3700954p3708616.html Sent from the PostgreSQL - > general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:01:50 am Alphadion wrote: > Quote: "Is all of this happening locally or is there a remote component?" > > I dont know how to define that... Are HM and the database both on the same machine or is the connection happening over the network? -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:04:30 am Bruce Momjian wrote: > The larger question is whether these forums should be used to help > people get support for commercial products that embed Postgres. > The eternal Open Source dilemma. Once it is released into the wild are we responsible for it? In a perfect world the folks that incorporate Postgres into their products would be entirely accountable for its care and feeding. Part of me says hold to that standard. Another part says how do you know where a question is really coming from? I for one am not going to interrogate everyone on their reasons for asking a question. The other issue is perception. Right or wrong, not answering questions ends up reflecting negatively on the community. -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
On Tuesday, March 15, 2011 8:50:34 am general_lee wrote: > Hi Guys, > > I'm trying to allow a remote host on our 10.3.55.X network remote access to > a Postgres Database on the same network. > > We're running Solaris 10 with Postgres 83 > > My postgresql.conf looks like this; > > > listen_addresses = '*' # what IP address(es) to listen on; > # comma-separated list of > addresses; # defaults to 'localhost', '*' = all port = 5432 > > > My pg_hba.conf looks like this; > > # TYPE DATABASEUSERCIDR-ADDRESS METHOD > > # "local" is for Unix domain socket connections only > local all all trust > # IPv4 local connections: > hostall all 127.0.0.1/32 trust > hostall all 10.3.55.0/24 255.255.255.0 trust > # IPv6 local connections: > hostall all ::1/128 trust > > > I restart Postgres with; > > svcadm restart svc:/application/database/postgresql_83:default_64bit > > But I get a "connection refused" if I try and telnet to port 5432 from a > remote host. Is this a "connection refused" from Postgres or telnet? > > What am I missing? > > Thanks, > -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:26:44 am Bruce Momjian wrote: > Adrian Klaver wrote: > > On Tuesday, March 15, 2011 9:04:30 am Bruce Momjian wrote: > > > The larger question is whether these forums should be used to help > > > people get support for commercial products that embed Postgres. > > > > The eternal Open Source dilemma. Once it is released into the wild are > > we responsible for it? In a perfect world the folks that incorporate > > Postgres into their products would be entirely accountable for its care > > and feeding. Part of me says hold to that standard. Another part says > > how do you know where a question is really coming from? I for one am > > not going to interrogate everyone on their reasons for asking a > > question. The other issue is perception. Right or wrong, not answering > > questions ends up reflecting negatively on the community. > > I think we should make sure they have checked the vendor help system > first or point them to that. Actually in this case that had already been done:) From the original post: "Whatever I did didn't help, incl. a teamviewer session with one of the HM administrators. I've already read and done everything about that in FAQ, forums,net etc. and nothing helps. " -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:31:17 am Merlin Moncure wrote: > On Tue, Mar 15, 2011 at 11:04 AM, Bruce Momjian wrote: > > The larger question is whether these forums should be used to help > > people get support for commercial products that embed Postgres. > > I have no particular philosophical objection to helping people using > Holdem Manager -- I'd venture most people posting here asking for help > are working on commercial software, and I'd prefer people to come away > from this forum with a positive impression. > > It's not like we are getting 100 emails a day on this stuff. This is > a participatory environment -- so what's the fuss? Not only that, but should a particular product show up consistently it becomes an opportunity, for those on this list that do offer commercial support, to make a 'warm' call. > That said, to the > OP, on this issue you might have better luck on irc channel with this > type of problem. > > merlin -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
