Re: [GENERAL] GSS Authentication
* greigw...@comcast.net (greigw...@comcast.net) wrote: > 2) Setup a new account in AD and used ktpass to create a keytab file for the > SPN. Did you make sure to use the right service name when creating the keytab? Can you do a klist -k on the keytab file and send the output? Does hostname --fqdn return the correct answer on the server? If not, you might need to adjust what PG thinks your FQDN is (there's an option in postgresql.conf for that too, but I'd recommend trying to fix your server to return the right answer instead of forcing it). > 3) Copied the keytab file onto my postgres server and updated my > postgresql.conf file appropriately (set the krb_server_keyfile to point to > the file I just created.) You'll probably also need to change the default service name to POSTGRES instead of postgres, in postgresql.conf too, klist -k should help figure that out. > Then I wrote a little test Perl program to connect to my postgres database. Can you test with psql locally first? Make sure that when you *try* to connect, it acquires the service princ from the KDC (check using klist) and then see if it is actually *able* to authenticate to the server. You'll need to set the appropriate environment variables on both Linux and Windows tho for libpq to know what the right service name is (again, POSTGRES instead of postgres, probably). You may also need to make sure that your default realm is set correctly and that your reverse DNS is working. Also, can you look in the PG server-side logs and see what errors are being reported there? There may be some during startup or when the client tries to connect that would be useful. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Cognitive dissonance
Robert Gravsj?? wrote: > >> I am for #1, not so much for #2, mainly on the grounds of size. But > >> given #1 it would be possible for packagers to make their own choices > >> about whether to include plain-text docs. > > > > Wouldn't it suffice to make it downloadable, like the pdf doc? > > And/or make the HTML version downloadable side by side with the PDF. That might be easy to do. We already build the HTML, and requiring people to recursively use wget is not user-friendly. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] libpq in Asynchronous mode and COPY
Michael Leib writes: > I'm using v8.4.4 and have an application written using libpq > in Asynchronous Command Mode and primarily dealing with the > COPY related apis. I have been successful in getting my application > working, but have come across an issue that I would like to determine > if I have done something wrong (probably), it's FAD (functioning as > designed) or a defect (doubtful). I think you're expecting one result too many. In the non-async case, there is not a separate result returned for PQputCopyEnd, so I'd not expect one for async mode either. PQputCopyEnd just returns an integer status code. The subsequent PGresult is all you'll get, in either normal or error cases. (It may be worth noting that PQputCopyEnd should be expected to succeed, perhaps after some retries, in any case except where the connection has been lost entirely. This does not have anything to do with whether the server failed the COPY command.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq in Asynchronous mode and COPY
Hi - I'm using v8.4.4 and have an application written using libpq in Asynchronous Command Mode and primarily dealing with the COPY related apis. I have been successful in getting my application working, but have come across an issue that I would like to determine if I have done something wrong (probably), it's FAD (functioning as designed) or a defect (doubtful). As an aside, I am using libev to handle my event processing. Basically, here is my question - I do the following: Issue "COPY tablename( columns ) FROM stdin with delimiter..." using PQsendQueryParams() and go into ready state.. I get notified that an event is pending and enter my switchboard I call PQconsumeInput() and PQtransactionStatus() returns PQTRANS_ACTIVE I check for PQisBusy() == 0 and then call PQgetResult() Then PQresultStatus() == PGRES_COPY_IN, so I start the using the COPY API's PQputCopyData, PQputCopyEnd and then call PQgetResult() to determine the status of PQputCopyEnd(). These are all successful and no errors are returned. I then go into ready state... I get notified that an event is pending and enter my switchboard Here is where it gets interesting and I am having the problem: 1) If there was an error returned from the server that the COPY failed (in my case, a foreign-key constraint was not met on one or more of my rows), after doing the normal PQconsumeInput(), PQtransactionStatus() and PQisBusy() checks (as described above), PQgetResult() returns the fatal error after examining the PGresult via PQresultStatus(). This is all good. 2) If there was NO ERROR (and all the rows appear in the target table within the db) my issue is that I DO get notified that the COPY completed (because I receive the pending event and my switchboard is called) but, after doing PQconsumeInput(), PQtransactionStatus() (which is PQTRANS_ACTIVE) and PQisBusy(), when I call PQgetResult() it returns NULL upon first call - I get no PGresult return. My issue/question is that I would expect to not have a NULL returned and, rather, I was expecting to see a PGresult with a status of PGRES_COMMAND_OK. Am I incorrect or is what I'm experiencing intended? The doc states: "After successfully calling PQputCopyEnd, call PQgetResult to obtain the final result status of the COPY command. One can wait for this result to be available in the usual way. Then return to normal operation" Normal operation, to me at least, means I would get a GPRES_COMMAND_OK. If I'm wrong, I apologize in advance. Just trying to see what I did wrong, if anything. Thanks, Michael -- 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] Best way to store case-insensitive data?
Yea this is a valid point. It's very possible my design won't work for the long term, and at some point I'll have to store the email name exactly as it was entered, and allow the lookup logic to be case insensitive with a lowercase index. However, I think the way I have it now should not break any known email server heh. Mike 2010/6/11 Michal Politowski : > On Thu, 10 Jun 2010 13:50:23 -0700, Mike Christensen wrote: >> I have a column called "email" that users login with, thus I need to >> be able to lookup email very quickly. The problem is, emails are >> case-insensitive. I want f...@bar.com to be able to login with >> f...@bar.com as well. There's two ways of doing this, that I can see: > > NB: technically the local part in an email address can be case sensitive. > As RFC 5321 says: > The local-part of a mailbox MUST BE treated as case sensitive. > Therefore, SMTP implementations MUST take care to preserve the case > of mailbox local-parts. In particular, for some hosts, the user > "smith" is different from the user "Smith". However, exploiting the > case sensitivity of mailbox local-parts impedes interoperability and > is discouraged. Mailbox domains follow normal DNS rules and are > hence not case sensitive. > > In practice I've yet to see a system having both smith and Smith > and them being different, but still it is theoretically posible. > > -- > Michał Politowski > Talking has been known to lead to communication if practiced carelessly. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GSS Authentication
I'm trying to get my PostgreSQL server on Linux configured so that I can
connect from a Windows client using GSS Authentication against Active
Directory. I found some helpful references on how to do this, but I'm still
coming up short. To summarize what I've done so far by way of configuration:
1) On the Linux server, setup my krb5.conf file such that I can get a ticket
from AD using kinit and confirm using klist.
2) Setup a new account in AD and used ktpass to create a keytab file for the
SPN.
3) Copied the keytab file onto my postgres server and updated my
postgresql.conf file appropriately (set the krb_server_keyfile to point to the
file I just created.)
Then I wrote a little test Perl program to connect to my postgres database.
use DBI;
use strict;
my $dbh =
DBI->connect('DBI:Pg:dbname=postgres;host=host.domain.com;krbsrvname=POSTGRES')
or die DBI->errstr;
When I try to run the Perl program I get this error:
DBI connect('dbname=postgres;host=host.domain.com;krbsrvname=POSTGRES')
failed: FATAL: accepting GSS security context failed
DETAIL: Miscellaneous failure: Unknown code ggss 3 at g.pl line 4
FATAL: accepting GSS security context failed
DETAIL: Miscellaneous failure: Unknown code ggss 3 at g.pl line 4
I then ramped up the debug logging on the postgres side and get this off the
server:
2010-06-11 17:23:49 EDTDEBUG: 0: Processing received GSS token of length
2119
2010-06-11 17:23:49 EDTLOCATION: pg_GSS_recvauth, auth.c:965
2010-06-11 17:23:49 EDTDEBUG: 0: gss_accept_sec_context major: 851968,
minor: -2045022973, outlen: 0, outflags: 7f
2010-06-11 17:23:49 EDTLOCATION: pg_GSS_recvauth, auth.c:984
2010-06-11 17:23:49 EDTFATAL: XX000: accepting GSS security context failed
2010-06-11 17:23:49 EDTDETAIL: Miscellaneous failure: Unknown code ggss 3
2010-06-11 17:23:49 EDTLOCATION: pg_GSS_error, auth.c:866
I'm using PostgreSQL 8.4.4 on Enterprise Linux 4.
Can anyone offer any suggestions?
Thanks in advance.
Greig
Re: [GENERAL] database response slows while pg_dump is running (8.4.2)
On Fri, Jun 11, 2010 at 08:43:53AM +0200, Adrian von Bidder wrote: > > Just speculation, I've not tried this. Perhaps pipe the output of pg_dump > through a software that bandwidth-limits the throughput? Perhaps. However, moving the pg_dump to a Slony slave has solved my problem. Thanks!! Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anyone know about PgMQ?
I was intrigued to see Chris Bohn's page about PgMQ ("Embedding
messaging in PostgreSQL") on the PGCon website at
http://www.pgcon.org/2010/schedule/events/251.en.html
I have also had a look at the pgfoundry site at
http://pgfoundry.org/projects/pgmq/ -- its empty.
I've tried to email Chris to find out more about his project, but
haven't received a response. Does any one have any details of this
project?
I am very interested in the possibility of linking Postgres events such
as triggers to RabbitMQ messaging queues.
Rory
--
Rory Campbell-Lange
r...@campbell-lange.net
--
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] Moving a live production database to different server and postgres release
Hi, Use one of the existent replication systems http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling#Replication p.s. I would highlight Slony, Londiste and Bucardo. On 11 June 2010 14:11, Ulas Albayrak wrote: > Hi, > > > > I’m in the process of moving our production database to a different physical > server, running a different OS and a newer release of postgreSQL. My problem > is that I’m not really sure how to go about it. > > > > My initial idea was to use WAL archiving to reproduce the db on the new > server and then get it up to date with the logs from the time of base backup > creation to the time the new server can get up. That was until I found out > WAL archiving doesn’t work between major postgreSQL releases. > > > > I can’t make a simple pg_dump – pg_restore and then redirect traffic when > the new server is up either, because during that time new data will have > been inserted in the original db. > > > > My best idea so far is to do a pg_dump and somehow archive all the DML in > the original db from that point in time for later insertion in the new db, > but I don’t know how that would be done practically. And I don’t even know > if that’s the best way to go, as I said, it’s only an idea. > > > > If anyone can give me some ideas on this, I’d be much obliged. > > > > Best Regards /Ulas -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- 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 show the current schema or search path in the psql PROMP
On Fri, Jun 11, 2010 at 2:18 PM, Tom Lane wrote: > Scott Marlowe writes: >> But that runs a shell command, how's that supposed to get the >> search_path? I've been trying to think up a solution to that and >> can't come up with one. > > Yeah, and you do *not* want the prompt mechanism trying to send SQL > commands... Would a more generic way to access pgsql settings in a \set prompt be useful? -- 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 show the current schema or search path in the psql PROMP
Scott Marlowe writes: > But that runs a shell command, how's that supposed to get the > search_path? I've been trying to think up a solution to that and > can't come up with one. Yeah, and you do *not* want the prompt mechanism trying to send SQL commands... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cognitive dissonance
Leif Biberg Kristensen skrev 2010-06-10 17.33: On Thursday 10. June 2010 17.24.00 Tom Lane wrote: Alvaro Herrera writes: Excerpts from Peter Eisentraut's message of jue jun 10 02:50:14 -0400 2010: As I said back then, doing this is straightforward, but we kind of need more than one user who asks for it before we make it part of a regular service, which comes with maintenance costs. Hey, count me as another interested person in a single-file plain-text doc output format. Well, there are two separate things here: * providing a Makefile target to build plain-text output. * shipping prebuilt plain text docs in standard distributions. I am for #1, not so much for #2, mainly on the grounds of size. But given #1 it would be possible for packagers to make their own choices about whether to include plain-text docs. Wouldn't it suffice to make it downloadable, like the pdf doc? And/or make the HTML version downloadable side by side with the PDF. There are good reasons for wanting access to the complete document when being offline. PDF is not such a bad format but it do have some limitations as have been previously mentioned. As for building the docs I don't think everyone, not even all developers, has the tool chain installed (or even wants to). Regards, roppert regards, -- 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] checkpoint spikes
On Thu, Jun 10, 2010 at 04:00:54PM -0400, Greg Smith wrote: >> 5. Does anybody know if I can set dirty_background_ratio to 0.5? As we >> have 12 GB RAM and rather slow disks 0,5% would result in a maximum of >> 61MB dirty pages. > > Nope. Linux has absolutely terrible controls for this critical > performance parameter. The sort of multi-second spikes you're seeing > are extremely common and very difficult to get rid of. Another relevent parameter is /proc/sys/vm/dirty_writeback_centisecs. By default linux only wakes up once every 5 seconds to check if there is stuff to write out. I have found that reducing this tends to smooth out bursty spikes. However, see: http://www.westnet.com/~gsmith/content/linux-pdflush.htm which indicates that kernel may try to defeat you here... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle signature.asc Description: Digital signature
Re: [GENERAL] How to show the current schema or search path in the psql PROMP
On 06/11/2010 11:10 AM, Scott Marlowe wrote: On Fri, Jun 11, 2010 at 11:29 AM, Adrian Klaver wrote: On 06/11/2010 10:23 AM, Joshua Tolley wrote: On Wed, Jun 09, 2010 at 05:52:49PM +0900, Schwaighofer Clemens wrote: Hi, I am trying to figure out how I can show the current search_path, or better the first search_path entry (the active schema) in the PROMPT variable for psql. Is there any way to do that? I couldn't find anything useful ... 5432 j...@josh# SHOW search_path; search_path "$user",public (1 row) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com From here: http://www.postgresql.org/docs/8.4/interactive/functions-info.html current_schema[()] Maybe combined with %`command` The output of command, similar to ordinary "back-tick" substitution. http://www.postgresql.org/docs/8.4/interactive/app-psql.html But that runs a shell command, how's that supposed to get the search_path? I've been trying to think up a solution to that and can't come up with one. I tried running a psql command using current_schema. It got the schema, unfortunately on a different session than the one I was in. So no it will not work. -- 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 show the current schema or search path in the psql PROMP
On Fri, Jun 11, 2010 at 11:29 AM, Adrian Klaver wrote: > On 06/11/2010 10:23 AM, Joshua Tolley wrote: >> >> On Wed, Jun 09, 2010 at 05:52:49PM +0900, Schwaighofer Clemens wrote: >>> >>> Hi, >>> >>> I am trying to figure out how I can show the current search_path, or >>> better the first search_path entry (the active schema) in the PROMPT >>> variable for psql. >>> >>> Is there any way to do that? I couldn't find anything useful ... >> >> 5432 j...@josh# SHOW search_path; >> search_path >> >> "$user",public >> (1 row) >> >> -- >> Joshua Tolley / eggyknap >> End Point Corporation >> http://www.endpoint.com > > From here: > http://www.postgresql.org/docs/8.4/interactive/functions-info.html > current_schema[()] > > Maybe combined with > %`command` > > The output of command, similar to ordinary "back-tick" substitution. > > http://www.postgresql.org/docs/8.4/interactive/app-psql.html But that runs a shell command, how's that supposed to get the search_path? I've been trying to think up a solution to that and can't come up with one. -- 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 show the current schema or search path in the psql PROMP
On Wed, Jun 9, 2010 at 2:52 AM, Schwaighofer Clemens wrote: > Hi, > > I am trying to figure out how I can show the current search_path, or > better the first search_path entry (the active schema) in the PROMPT > variable for psql. > > Is there any way to do that? I couldn't find anything useful ... http://www.postgresql.org/docs/8.3/static/app-psql.html#APP-PSQL-PROMPTING There are lots of things you can put into a prompt, but that doesn't appear to be one of them. Seems like a reasonable feature request. -- 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] Enforcing password standards
Thanks everyone, I will wait for Postgres 9.0 to implement this feature then. Thanks Thanks Deepak On Fri, Jun 11, 2010 at 10:30 AM, Joshua Tolley wrote: > On Thu, Jun 10, 2010 at 06:01:24PM -0700, DM wrote: > >How to force postgres users to follow password standards and renewal > >policies? > >Thanks > >Deepak > > 9.0 will ship with a contrib module called "passwordcheck" which will > enforce > some of these things, FWIW. > > -- > Joshua Tolley / eggyknap > End Point Corporation > http://www.endpoint.com > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkwScpkACgkQRiRfCGf1UMMOzgCfW1P8SpFR53OSjm/og3hQFjba > 0dIAoJK9mkm07XCAyfnPeiygBgrKuFG2 > =XESJ > -END PGP SIGNATURE- > >
Re: [GENERAL] Enforcing password standards
On Thu, Jun 10, 2010 at 06:01:24PM -0700, DM wrote: >How to force postgres users to follow password standards and renewal >policies? >Thanks >Deepak 9.0 will ship with a contrib module called "passwordcheck" which will enforce some of these things, FWIW. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] How to show the current schema or search path in the psql PROMP
On 06/11/2010 10:23 AM, Joshua Tolley wrote: On Wed, Jun 09, 2010 at 05:52:49PM +0900, Schwaighofer Clemens wrote: Hi, I am trying to figure out how I can show the current search_path, or better the first search_path entry (the active schema) in the PROMPT variable for psql. Is there any way to do that? I couldn't find anything useful ... 5432 j...@josh# SHOW search_path; search_path "$user",public (1 row) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com From here: http://www.postgresql.org/docs/8.4/interactive/functions-info.html current_schema[()] Maybe combined with %`command` The output of command, similar to ordinary "back-tick" substitution. http://www.postgresql.org/docs/8.4/interactive/app-psql.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] How to show the current schema or search path in the psql PROMP
On Wed, Jun 09, 2010 at 05:52:49PM +0900, Schwaighofer Clemens wrote: > Hi, > > I am trying to figure out how I can show the current search_path, or > better the first search_path entry (the active schema) in the PROMPT > variable for psql. > > Is there any way to do that? I couldn't find anything useful ... 5432 j...@josh# SHOW search_path; search_path "$user",public (1 row) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] checkpoint spikes
Janning wrote: most docs I found relates to 8.2 and 8.3. In Things of checkpoints, is 8.4 comparable to 8.3? It would be nice if you update your article to reflect 8.4 There haven't been any changes made in this area since 8.3, that's why there's been no update. 8.4 and 9.0 have exactly the same checkpoint behavior and background writer behavior as 8.3 from the perspective of regular use. The changes made only impact how things happen on a replication standby, not the master server. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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 response slows while pg_dump is running (8.4.2)
On 06/10/2010 11:43 PM, Adrian von Bidder wrote: Just speculation, I've not tried this. Perhaps pipe the output of pg_dump through a software that bandwidth-limits the throughput? (I don't know if such a command exists, pv (pipe view) Allows you to monitor rate of transfers through a pipe. Also has options to specify max transfer rate (-L). It's handy for quick-n-dirty disk-rate tests - mostly only useful for bulk read (pv /some/big/file > /dev/null) Or monitor the progress of your dump: pg_dump | pv > your.dump May not be installed by default - "apt-get install pv" works for Ubuntu but for CentOS/RHEL but you may need to get it from rpmforge or use the source: http://www.ivarch.com/programs/pv.shtml Cheers, Steve -- 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] Best way to store case-insensitive data?
On Thu, 10 Jun 2010 13:50:23 -0700, Mike Christensen wrote: > I have a column called "email" that users login with, thus I need to > be able to lookup email very quickly. The problem is, emails are > case-insensitive. I want f...@bar.com to be able to login with > f...@bar.com as well. There's two ways of doing this, that I can see: NB: technically the local part in an email address can be case sensitive. As RFC 5321 says: The local-part of a mailbox MUST BE treated as case sensitive. Therefore, SMTP implementations MUST take care to preserve the case of mailbox local-parts. In particular, for some hosts, the user "smith" is different from the user "Smith". However, exploiting the case sensitivity of mailbox local-parts impedes interoperability and is discouraged. Mailbox domains follow normal DNS rules and are hence not case sensitive. In practice I've yet to see a system having both smith and Smith and them being different, but still it is theoretically posible. -- Michał Politowski Talking has been known to lead to communication if practiced carelessly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Moving a live production database to different server and postgres release
Hi, I'm in the process of moving our production database to a different physical server, running a different OS and a newer release of postgreSQL. My problem is that I'm not really sure how to go about it. My initial idea was to use WAL archiving to reproduce the db on the new server and then get it up to date with the logs from the time of base backup creation to the time the new server can get up. That was until I found out WAL archiving doesn't work between major postgreSQL releases. I can't make a simple pg_dump - pg_restore and then redirect traffic when the new server is up either, because during that time new data will have been inserted in the original db. My best idea so far is to do a pg_dump and somehow archive all the DML in the original db from that point in time for later insertion in the new db, but I don't know how that would be done practically. And I don't even know if that's the best way to go, as I said, it's only an idea. If anyone can give me some ideas on this, I'd be much obliged. Best Regards /Ulas
Re: [GENERAL] Moving a live production database to different server and postgres release
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > My best idea so far is to do a pg_dump and somehow archive all the DML > in the original db from that point in time for later insertion in the > new db, but I dont know how that would be done practically. And I > dont even know if thats the best way to go, as I said, its only an > idea. What you need is a replication system. Take a look at Slony or Bucardo. Basically, you copy over everything except for the data to the new database, switch the replication system on, let it catch up, then stop apps from hitting the server, wait for the new one to catch up, and point your apps to the new one. Important factors that you left out are exactly how big your database is, what version you are on, what version you are moving to, and how busy your system is. Also keep in mind that both Bucardo and Slony are trigger based on primary keys or unique indexes, so tables without such constraints cannot be replicated: you'll need to either add a unique constraint to the tables, or copy them separately (e.g. pg_dump -t tablename or Bucardo's fullcopy mode). If you weren't also moving your OS and server, pg_migrator (aka pg_upgrade) might work for you as well: it does an inplace, one-time upgrade but only supports a limited number of versions at the moment. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201006110927 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkwSOk8ACgkQvJuQZxSWSsgt6QCfYgx6mBibJjNNY88iPBOJNmSL +FAAoLEVuYUw/VJWg3tRC25VH+ZrNsiH =yhFJ -END PGP SIGNATURE- -- 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] Partial indexes instead of partitions
> Well the situation is still ambiguous > so: > Is it possible to provide this table and indexes definitions? > And it > would be great it you describe the queries you are going to do > on this table > or just provide the SQL. Sure! Basically what I'm trying to do is to partition the index in the table where the data is going to be inserted into smaller indexes, but without using partitions: I would use partial indexes. "Historic" data will have just the big index... say that I want to store 1G rows: 100M per day, 10 days. I would have 10 tables, 9 of them with 2 big indexes (the indexes on the 2 columns that are going to be used in queries together with the timestamp) and the latest one with 24*2 smaller indexes (so that insertion will still be fast) to be dropped overnight after the 2 big indexes have been created... then a new table is created (for the new day's data) with the small indexes and the oldest table dropped (as I said, I won't store more than 10 days). This is "pseudo SQL": CREATE TABLE master ( ts timestamp, key1 bigint, <-- populated with almost-random values key2 bigint, <-- populated with almost-random values data1 varchar(20), [...] ); CREATE TABLE master_01 ( CHECK ( ts >= DATE '2006-03-01' AND ts < DATE '2006-03-02' ) ) INHERITS (master); CREATE INDEX master_01_ix1 ON master_01 (key1); CREATE INDEX master_01_ix2 ON master_01 (key2) CREATE TABLE master_02 ( CHECK ( ts >= DATE '2006-03-02' AND ts < DATE '2006-03-03' ) ) INHERITS (master); CREATE INDEX master_02_ix1 ON master_02 (key1); CREATE INDEX master_02_ix2 ON master_02 (key2) [10 tables like the above...] With this config insertion on the "today's" table will be slow at the end of the day, because updating the 2 indexes will be very slow (they will be getting very large). So I thought I could make, on "today's table", instead of the 2 indexes on the whole table, something like: CREATE INDEX master_10_1_ix1 ON master_10 (key1) WHERE (ts >= DATETIME '2006-03-10 00:00' and ts < DATETIME '2006-03-10 01:00') (same thing for second indexed column) CREATE INDEX master_10_2_ix1 ON master_10 (key1) WHERE (ts >= DATETIME '2006-03-10 01:00' and ts < DATETIME '2006-03-10 02:00') (same thing for second indexed column) [other 22 indexes definition like the above, one per hour...] That is, the table where data will be inserted (ts will always be ascending, so I will always insert data in the latest table) will have multiple small indexes. Then, at night, the small indexes would be dropped after one big index has been created (since no more rows will be inserted in that table, I don't care if the index is big). So, a query like: select * from master where key1=938479 and ts between now() and "now()-10 minutes" would use the proper index on the "today's" table; a query like: select * from master where key1=938479 and ts between "3 days ago" and "2 days ago" would use the indexes in table "today minus 2 days" and "today minus 3 days" -- 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] PL/pgSQL nested functions
> Are there any plans to allow PL/pgSQL functions to be nested like Oracle > allows with PL/SQL procedures? > > If not, what are the best ways to convert PL/SQL nested procedures to > PL/pgSQL? > > PostgreSQL plus advanced server (which is a proprietary derivative of postgres) has oracle compatibility features. I'm not specifically aware that it supports nested procedures, but it may well. -- Regards, Peter Geoghegan -- 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] Partial indexes instead of partitions
On 11 June 2010 16:29, Leonardo F wrote: > >> Could you please explain the reason to do so many >> partitions? > > > Because otherwise there would be tons of rows in each > partition, and randomly "updating" the index for that many > rows 2000 times per second isn't doable (the indexes > get so big that it would be like writing a multi-GB file > randomly) > >> In case b) you will face a huge overhead related to necessity >> of >> checking all the data in the table every time new index is >> created > > > I would create the table with all the indexes already in; but only > > the index related to the "current timestamp of the inserted row" > would be updated; the others wouldn't be touched. Well the situation is still ambiguous so: Is it possible to provide this table and indexes definitions? And it would be great it you describe the queries you are going to do on this table or just provide the SQL. -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- 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] Partial indexes instead of partitions
> Could you please explain the reason to do so many > partitions? Because otherwise there would be tons of rows in each partition, and randomly "updating" the index for that many rows 2000 times per second isn't doable (the indexes get so big that it would be like writing a multi-GB file randomly) > In case b) you will face a huge overhead related to necessity > of > checking all the data in the table every time new index is > created I would create the table with all the indexes already in; but only the index related to the "current timestamp of the inserted row" would be updated; the others wouldn't be touched. -- 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] Partial indexes instead of partitions
On 11 June 2010 13:00, Leonardo F wrote: > a) create 480 partitions, 1 for each hour of the day. 2 indexes on each > partition > b) create 20 partitions, and create 24*2 partial indexes on the current > partition; then the next day (overnight) create 2 global indexes for the > table and drop the 24*2 indexes... > > I thought about option b) because I don't like the fact that the planner takes > "ages" to plan a query in case there are 480 partitions; in option b) I would > have: > > 19 partitions with 2 indexes each > 1 partition (the "current day" one) with 24*2 partial indexes Could you please explain the reason to do so many partitions? In case b) you will face a huge overhead related to necessity of checking all the data in the table every time new index is created (doesn't matter it is partial). -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- 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] Can ARRAY( ... ) generate text[][]?
On Thu, Jun 10, 2010 at 6:59 PM, J. Greg Davidson wrote: > Hi fellow PostgreSQL hackers! > > I tried to write an SQL glue function to turn an array > of alternating key/value pairs into an array of arrays > and got the message > > ERROR: 42704: could not find array type for data type text[] I do it like this: create type pair_t as (key text, value text); > -- BEGIN CODE > > -- Here's a simplified example: > > CREATE OR REPLACE > FUNCTION text__(variadic text[]) RETURNS text[][] AS $$ > SELECT ARRAY( > SELECT ARRAY[ $1[i], $1[i+1] ] > FROM generate_series(1, array_upper($1,1), 2) i > ) > $$ LANGUAGE sql; create or replace function pairs(variadic text[]) returns pair_t[] as $$ select array(select ($1[i], $1[i+1])::pair_t FROM generate_series(1, array_upper($1,1), 2) i) $$ language sql immutable; merlin -- 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] Moving a live production database to different server and postgres release
On 11/06/2010 11:24, Ulas Albayrak wrote: > My initial idea was to use WAL archiving to reproduce the db on the > new server and then get it up to date with the logs from the time of > base backup creation to the time the new server can get up. That was > until I found out WAL archiving doesn’t work between major postgreSQL > releases. Slony-I (http://www.slony.info) can handle different major releases, allowing you to switch from one server to another in seconds. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Moving a live production database to different server and postgres release
Hi, I’m in the process of moving our production database to a different physical server, running a different OS and a newer release of postgreSQL. My problem is that I’m not really sure how to go about it. My initial idea was to use WAL archiving to reproduce the db on the new server and then get it up to date with the logs from the time of base backup creation to the time the new server can get up. That was until I found out WAL archiving doesn’t work between major postgreSQL releases. I can’t make a simple pg_dump – pg_restore and then redirect traffic when the new server is up either, because during that time new data will have been inserted in the original db. My best idea so far is to do a pg_dump and somehow archive all the DML in the original db from that point in time for later insertion in the new db, but I don’t know how that would be done practically. And I don’t even know if that’s the best way to go, as I said, it’s only an idea. If anyone can give me some ideas on this, I’d be much obliged. Best Regards /Ulas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partial indexes instead of partitions
HI all, I have a very big table (2000 inserts per sec, I have to store 20 days of data). The table has 2 indexes, in columns that have almost-random values. Since keeping those two indexes up-to-date can't be done (updating 2000 times per second 2 indexes with random values on such a huge table is impossible) I thought that partitioning was the way to go. Now I think I have 2 options: a) create 480 partitions, 1 for each hour of the day. 2 indexes on each partition b) create 20 partitions, and create 24*2 partial indexes on the current partition; then the next day (overnight) create 2 global indexes for the table and drop the 24*2 indexes... I thought about option b) because I don't like the fact that the planner takes "ages" to plan a query in case there are 480 partitions; in option b) I would have: 19 partitions with 2 indexes each 1 partition (the "current day" one) with 24*2 partial indexes Does it make sense? Anyone has ever used partial indexes instead of partitions? -- 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] checkpoint spikes
On Thursday 10 June 2010 22:00:54 Greg Smith wrote: > Janning wrote: > > 1. With raising checkpoint_timeout, is there any downgrade other than > > slower after-crash recovery? > > Checkpoint spikes happen when too much I/O has been saved up for > checkpoint time than the server can handle. While this is normally > handled by the checkpoint spreading logic, you may find that with your > limited disk configuration there's no other way to handle the problem > but to make checkpoints much more frequent, rather than slower. Uhh! I had so much success with less frequent checkpoints. At least the spike is the same but it does not happen so often. > At > http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm I > write about how you had to tune PostgreSQL 8.0 to 8.2 in order to keep > checkpoint spikes from getting too big. You may have to follow those > same techniques for your server. Just don't try to increase the > background writer settings in your case--the 8.3 one is different enough > that you can't tune that the way was suggested for 8.2. we are runing 8.4 and of course I read your article. I just raised bgwriter_lru_maxpage to 200 :-( most docs I found relates to 8.2 and 8.3. In Things of checkpoints, is 8.4 comparable to 8.3? It would be nice if you update your article to reflect 8.4. > > 2. Is there a way to calculate the after-crash recovery time with a > > certain checkpoint_timeout? How long would be approx. for a > > checkpoint_timeout of 60 minutes? > > Simulate it. No way to estimate. Ok. won't try it now :-) > > 3. Is it sane to set checkpoint_timeout to 120min or even to 600min? > > Checkpoints happen when you reach either checkpoint_segments of WAL > written *or* reach checkpoint_timeout, whichever happens first. Sorry the question was not precise. I already raised checkpoint_segments to reach that goal of less frequent checkpoints. > You'd > have to set both to extremely large values to get checkpoints to happen > really infrequently. Which I suspect is the exactly opposite of what > you want--you can't handle the spike from a long delayed checkpoint, and > probably want to tune for shorter and smaller ones instead. > > Every now and then we run into someone who had to retune their system to > something like: > > shared_buffers=512MB > checkpoint_segments=3 > > In order to avoid spikes from killing them. That may be the direction > you have to head. The longer the time between checkpoints, the bigger > the spike at the end is going to be to some extend; you can't completely > spread that out. I am really afraid of doing it right now. In my experience the spike is the same but we only have it once an hour. > > 5. Does anybody know if I can set dirty_background_ratio to 0.5? As we > > have 12 GB RAM and rather slow disks 0,5% would result in a maximum of > > 61MB dirty pages. > > Nope. Linux has absolutely terrible controls for this critical > performance parameter. The sort of multi-second spikes you're seeing > are extremely common and very difficult to get rid of. ok, thanks. > > PS: Do I need to post this question on pgsql-perfomance? If so, please > > let me know. > > That would have been the better list for it originally. I also wrote > something about a technique that uses pg_stat_bgwriter snapshots to help > model what the server is doing in these cases better you might find > useful on the admin list, it's at > http://archives.postgresql.org/pgsql-admin/2010-06/msg00074.php thank you very much for your help! best regards Janning -- 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] Best way to store case-insensitive data?
Yup, I actually ended up doing this with this constraint: ALTER TABLE Users ADD CONSTRAINT check_email CHECK (email ~ E'^[^A-Z]+$'); However, I like your version better so I'll use that instead :) Mike On Thu, Jun 10, 2010 at 11:48 PM, Adrian von Bidder wrote: > Heyho! > > On Thursday 10 June 2010 22.50:23 Mike Christensen wrote: >> 2) Every time the user updates or saves their email, store it in >> lowercase, and every time I lookup an email, pass in a lowercase >> email. > > I'd do it this way. Plus either a CHECK condition on the table (email = > lowercase(email)) (this will reliably catch all cases, but you will > experience failures until you have found all cases) > > Or a BEFORE trigger that converts email to lowercase. (This is mostly > transparent for storing, but I usually try to avoid triggers that modify > data like this. But that's probably just me.) > > In either case, obviously you'll still need to change the code that is used > for retrieving and comparing email addresses. > > cheers > -- vbi > > -- > featured link: http://www.pool.ntp.org > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
