[GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index
I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3. Here's the error: psql:/srv/pg_upgrade_dump_globals.sql:54: ERROR: duplicate key value violates unique constraint "pg_authid_oid_index" DETAIL: Key (oid)=(10) already exists. Any ideas what I'm doing wrong? Here's the verbose output from pg_upgrade: $ /opt/postgresql-9.1/bin/pg_upgrade --link --verbose --old-datadir=/srv/postgresql/pg_data --new-datadir=/srv/postgres-9.1 --old-bindir=/opt/postgresql-9.0/bin --new-bindir=/opt/postgresql-9.1/bin Running in verbose mode Performing Consistency Checks - Checking current, bin, and data directories ok Checking cluster versions ok "/opt/postgresql-9.0/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgresql/pg_data" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=20" start >> "/dev/null" 2>&1 Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system oid user data typesok Checking for contrib/isn with bigint-passing mismatch ok Creating catalog dump "/opt/postgresql-9.1/bin/pg_dumpall" --port 5432 --username "postgres" --schema-only --binary-upgrade > "/srv/pg_upgrade_dump_all.sql" ok "/opt/postgresql-9.0/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgresql/pg_data" stop >> "/dev/null" 2>&1 "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1" -o "-p 5432 -b" start >> "/dev/null" 2>&1 Checking for prepared transactions ok Checking for presence of required libraries ok | If pg_upgrade fails after this point, you must | re-initdb the new cluster before continuing. | You will also need to remove the ".old" suffix | from /srv/postgresql/pg_data/global/pg_control.old. Performing Upgrade -- Adding ".old" suffix to old global/pg_control ok Analyzing all rows in the new cluster "/opt/postgresql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all --analyze >> "/dev/null" 2>&1 ok Freezing all rows on the new cluster "/opt/postgresql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all --freeze >> "/dev/null" 2>&1 ok "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1" stop >> "/dev/null" 2>&1 Deleting new commit clogs ok Copying old commit clogs to new server cp -Rf "/srv/postgresql/pg_data/pg_clog" "/srv/postgres-9.1/pg_clog" ok Setting next transaction id for new cluster "/opt/postgresql-9.1/bin/pg_resetxlog" -f -x 743542427 "/srv/postgres-9.1" > /dev/null ok Resetting WAL archives "/opt/postgresql-9.1/bin/pg_resetxlog" -l 1,829,15 "/srv/postgres-9.1" >> "/dev/null" 2>&1 ok "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1" -o "-p 5432 -b" start >> "/dev/null" 2>&1 Setting frozenxid counters in new cluster ok Creating databases in the new cluster "/opt/postgresql-9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port 5432 --username "postgres" -f "/srv/pg_upgrade_dump_globals.sql" --dbname template1 >> "/dev/null" psql:/srv/pg_upgrade_dump_globals.sql:54: ERROR: duplicate key value violates unique constraint "pg_authid_oid_index" DETAIL: Key (oid)=(10) already exists. There were problems executing "/opt/postgresql-9.1/bin/psql" --set ON_ERROR_STOP=on --no-psqlrc --port 5432 --username "postgres" -f "/srv/pg_upgrade_dump_globals.sql" --dbname template1 >> "/dev/null" Failure, exiting "/opt/postgresql-9.1/bin/pg_ctl" -w -l "/dev/null" -D "/srv/postgres-9.1" -m fast stop >> "/dev/null" 2>&1 Thanks, Bryan
Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index
On Thu, May 31, 2012 at 4:28 PM, Jeff Davis wrote: > On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote: > > I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3. Here's > > the error: > > Please send /srv/pg_upgrade_dump_globals.sql > > Also, can you restart the old system (by removing the ".old" suffix, as > the message suggests), and then do a "SELECT oid,* FROM pg_authid" and > send the output along? > Here's the requested data: https://gist.github.com/2852014 I had to censor some of it because it contained sensitive information, hopefully the censoring is obvious and I don't believe I touched any of the functional information. Thanks, Bryan
Re: [GENERAL] pg_upgrade from 9.0.7 to 9.1.3: duplicate key pg_authid_oid_index
On Fri, Jun 1, 2012 at 8:07 AM, Bryan Murphy wrote: > On Thu, May 31, 2012 at 4:28 PM, Jeff Davis wrote: > >> On Thu, 2012-05-31 at 15:55 -0500, Bryan Murphy wrote: >> > I'm having a problem upgrading a cluster from 9.0.7 to 9.1.3. Here's >> > the error: >> >> Please send /srv/pg_upgrade_dump_globals.sql >> >> Also, can you restart the old system (by removing the ".old" suffix, as >> the message suggests), and then do a "SELECT oid,* FROM pg_authid" and >> send the output along? >> > > Here's the requested data: https://gist.github.com/2852014 > > I had to censor some of it because it contained sensitive information, > hopefully the censoring is obvious and I don't believe I touched any of the > functional information. > OK, I seem to have figured it out. Your questions pointed me in the right direction. The old 9.0 cluster was created by ubuntu. In this cluster there was an ubuntu user with an oid of 10 and a postgres user with an oid of 16386. The new 9.1 cluster was created with a custom build of postgres 9.1. This did not have an ubuntu user, and it had a postgres user with an oid of 10. I renamed the postgres user in the old 9.0 cluster to pg, renamed the ubuntu user to postgres, and then re-ran pg_upgrade and it appears to have worked correctly this time. Bryan
[GENERAL] Deleted Flag/Unique Constraint
Is it possible to declare a unique constraint in combination with a deleted flag? For example, if I have a table like this: CREATE TABLE ( ID NOT NULL PRIMARY KEY, Key VARCHAR(32) NOT NULL, Value VARCHAR(32) NOT NULL, Deleted INT NOT NULL DEFAULT 0 ); can I declare a unique constraint that only checks Key and Value when Deleted = 0? Thanks, Bryan
Re: [GENERAL] Deleted Flag/Unique Constraint
Thanks! That works great! Bryan On 3/29/07, Jonathan Hedstrom <[EMAIL PROTECTED]> wrote: Bryan Murphy wrote: > Is it possible to declare a unique constraint in combination with a > deleted flag? > > For example, if I have a table like this: > > CREATE TABLE > ( > ID NOT NULL PRIMARY KEY, > Key VARCHAR(32) NOT NULL, > Value VARCHAR(32) NOT NULL, > Deleted INT NOT NULL DEFAULT 0 > ); > > can I declare a unique constraint that only checks Key and Value when > Deleted = 0? Yes, something like this: CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE deleted=0; -Jonathan
Re: [GENERAL] Deleted Flag/Unique Constraint
I think the other guys suggestion will work better. ;) Really, the table was just an example off the top of my head. I believe we do use a boolean as the deleted flag. We primarily use it to track the user who originally created an item (even if their account is deleted). It's a bit like a financial system where you can't retroactively change the data. We always want to know who was associated with the original transaction, even long after their account was deleted. Thanks for the suggestion though! Bryan On 3/29/07, John D. Burger <[EMAIL PROTECTED]> wrote: On Mar 29, 2007, at 17:39, Bryan Murphy wrote: > Is it possible to declare a unique constraint in combination with a > deleted flag? > For example, if I have a table like this: > > CREATE TABLE > ( > ID NOT NULL PRIMARY KEY, > Key VARCHAR(32) NOT NULL, > Value VARCHAR(32) NOT NULL, > Deleted INT NOT NULL DEFAULT 0 > ); > > can I declare a unique constraint that only checks Key and Value > when Deleted = 0? I gather you want the constraint enforced only if the item is not deleted - duplicate pairs are fine if all but one are deleted, yes? You can't do this with this table definition as is, but ... Unique constraints only apply to groups of column values that are non- null, so, if you'd be willing to do something like the following, I think you can get roughly what you want: CREATE TABLE keyValues -- Dunno what name you wanted ( ID serial PRIMARY KEY, -- Dunno what type you wanted Key VARCHAR(32) NOT NULL, Value VARCHAR(32) NOT NULL, notDeleted Boolean DEFAULT True, UNIQUE (Key, Value, notDeleted), CHECK (notDeleted = True) ); I've changed your flag to be a boolean - this is not strictly necessary, but conveys its semantics better, I think. More importantly, I've reversed its polarity, and arranged for it to only have values of True or NULL. When it is NULL, which we can interpret as meaning it is not notDeleted, i.e., it is deleted, the unique constraint will not apply. This is an abuse of NULL, and means that your app will have to set the flag to NULL, rather than False, but I believe it satisfies your requirements. You could hide the abuse behind a view, if necessary. - John Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Deleted Flag/Unique Constraint
Yeah, personally, I'm not entire 100% convinced of the concept myself and am actively investigating alternatives. Most of what I'm doing right now is simple research, as well as trying out a few ideas to see what works in practice and what doesn't. Unfortunately, this is one of those areas where I have yet to find a lot of guidance on the issue. Bryan On 3/29/07, Lew <[EMAIL PROTECTED]> wrote: Bryan Murphy wrote: > I think the other guys suggestion will work better. ;) > > Really, the table was just an example off the top of my head. I believe > we do use a boolean as the deleted flag. We primarily use it to track > the user who originally created an item (even if their account is > deleted). It's a bit like a financial system where you can't > retroactively change the data. We always want to know who was associated > with the original transaction, even long after their account was deleted. > Thanks for the suggestion though! (Your post would have been clearer if it were inline with the material quoted, rather than top-posted.) Your requirements analysis raises an important point, one that I've seen misused in practice. In your data model, "deleted" is a business concept - the example "deleted account" is an account that is NOT deleted in the database, but exists with a business attribute "deleted". I've seen that sort of flag used absent any business requirement to maintain current information about a "deleted" fact, but instead as meta-data to audit the database usage. The bad effect was that all business logic had to account for the "deleted" flag even though it had no semantic in the business domain. I speculate that a separate meta-data table is more apt for such a use, although I continue to investigate scenarios where it makes sense to keep historic facts in the same table with current facts. Naturally this opens up the world of temporal databases. My hypothesis is that the business-domain semantics of the facts that a table models must be temporal in order to keep historic facts therein, If the purpose for the history is not rooted in the business domain, then such facts must be in separate tables from those that model the business domain. So a "deleted" account attribute merits a column in the "accounts" table, but record deletion facts just to monitor database usage should be separate. Both might make sense in the same implementation, yielding "accounts" and "accountsaudit" tables. I am not yet convinced that I have the answers on this matter. -- Lew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Hit by the out of memory killer last night
Last night we were hit by the out of memory killer. Looking at the following graph, you can clearly see unusual memory growth. This is a database server running Postgres 9.0.0. http://mediafly-public.s3.amazonaws.com/dbcluster02-master-month.png We have another server, running Postgres 9.0.1 which showing a similar behavior pattern: http://mediafly-public.s3.amazonaws.com/dbcluster03-master-month.png Compare these to another server which is running 8.3.7 and does not exhibit this behavior: http://mediafly-public.s3.amazonaws.com/dbcluster01-master-month.png We're going to accelerate our upgrade to 9.0.2 for these servers, however, I'm concerned that we have not identified the source of the memory leak and this upgrade won't necessarily fix the problem. Any advice? What should I be looking for? Thanks, Bryan
Re: [GENERAL] Hit by the out of memory killer last night
On Mon, Jan 31, 2011 at 10:35 AM, Ben Chobot wrote: > > Any advice? What should I be looking for? > > Any particular reason you are running the OOM killer on a database server? > Why have the kernel set to overcommit memory in the first place? Simply an oversight. That being said, it does not explain WHY we are seeing such a memory usage pattern. Thanks, Bryan
Re: [GENERAL] about c# and postgresql
I highly recommend you use the Npgsql driver, and if you're feeling really saucy try NHibernate on top of that. http://pgfoundry.org/projects/npgsql http://www.nhibernate.org/ Bryan On 7/23/07, longlong <[EMAIL PROTECTED]> wrote: hi,all i have a local system with windows xp. i want to use c# to connect with a postgresql database running in a linux system. i get libpg.dll and PgOleDb.dll. that's enough for me to do so? or could you tell me how?
Re: [GENERAL] Optimising SELECT on a table with one million rows
First question... did you create the appropriate indexes on the appropriate columns for these tables? Foreign keys do not implicitly create indexes in postgres. Bryan On 7/30/07, Cultural Sublimation <[EMAIL PROTECTED]> wrote: > > Hi, > > I'm fairly new with Postgresql, so I am not sure if the performance > problems I'm having are due to poorly constructed queries/indices, > or if I bumped into more fundamental problems requiring a design of > my database structure. That's why I'm requesting your help. > > Here's the situation: I have three tables: Users, Stories, and Comments. > Stories have an author (a user), and a comment is associated with a > story and with the user who posted it. The structure of the database > is therefore fairly simple: (there are also some sequences, which I've > omitted for clarity) > > > CREATE TABLE users > ( > user_id int UNIQUE NOT NULL, > user_name text, > PRIMARY KEY (user_id) > ); > > > CREATE TABLE stories > ( > story_idint UNIQUE NOT NULL, > story_title text, > story_body text, > story_timestamp timestamptz, > story_authorint REFERENCES users (user_id) NOT NULL, > PRIMARY KEY (story_id) > ); > > > CREATE TABLE comments > ( > comment_id int UNIQUE NOT NULL, > comment_title text, > comment_bodytext, > comment_timestamp timestamptz, > comment_story int REFERENCES stories (story_id) NOT > NULL, > comment_author int REFERENCES users (user_id) NOT NULL, > PRIMARY KEY (comment_id) > ); > > > I've also populated the database with some test data, comprising 1,000 > users, 1,000 stories (one per user), and 1,000,000 comments (one comment > per user per story). > > Now, the query I wish to optimise is also simple: get me all comments (the > comment_id suffices) and corresponding user *names* for a given story. > If for example the story_id is 100, the query looks like this: > > SELECT comments.comment_id, users.user_name > FROM comments, users > WHERE comments.comment_story = 100 AND comments.comment_author = > users.user_id; > > The problem is that this query takes a *very* long time. With the said > 1,000,000 comments, it needs at least 1100ms on my system. "Explain > analyze" tells me that a sequential scan is being performed on both > users and comments: > > Hash Join (cost=28.50..21889.09 rows=988 width=14) (actual > time=3.674..1144.779 rows=1000 loops=1) >Hash Cond: ((comments.comment_author)::integer = (users.user_id > )::integer) >-> Seq Scan on comments (cost=0.00..21847.00 rows=988 width=8) > (actual > time=0.185..1136.067 rows=1000 loops=1) > Filter: ((comment_story)::integer = 100) >-> Hash (cost=16.00..16.00 rows=1000 width=14) (actual time= > 3.425..3.425 > rows=1000 loops=1) > -> Seq Scan on users (cost=0.00..16.00 rows=1000 width=14) > (actual > time=0.068..1.845 rows=1000 loops=1) > Total runtime: 1146.424 ms > > On the long run, I guess one possible solution to this problem will be > to partition the comments table into a number of sub-tables, most likely > based on the timestamp attribute (by having current versus historic data). > Nevertheless, I am wondering if there are other more straightforward ways > to optimise this query. Some clever use of indices, perhaps? Or is > the way I am now constructing the select non-optimal? Or do I need > some pixie-magic on the Postgresql settings? Anyway, any suggestions > are welcome! (and thanks in advance) > > Regards, > C.S. > > > > > > > Yahoo! oneSearch: Finally, mobile search > that gives answers, not web links. > http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ >
[GENERAL] Index Usage
Is there a way I can track index usage over a long period of time? Specifically, I'd like to identify indexes that aren't being regularly used and drop them. Bryan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Index Usage
On 10/17/07, Joao Miguel Ferreira <[EMAIL PROTECTED]> wrote: > If your intention is to eliminate the unused indexes rows you should run > 'vaccum' and/or 'vacuum full' and/or 'reindex'. > > This also has the consequence of freing filesystem space and returning > it back to the OS. > > Check it out here: > > http://www.postgresql.org/docs/8.1/static/maintenance.html > > chapters 22.1, 22.2 and 22.3 > > I use: > > VACUUM FULL ANALYZE; > REINDEX INDEX yourIndex; > REINDEX TABLE yourTable > > it works just great for me. > > Cheers > joao That's not my intention at all. My intention is to justify the validity of each index in our database. Some indexes have snuck in that I find of questionable value, and I want the data to backup my intuition. Anyway, I'll look into the pg_stat* tables and see if those give me the data I want. Thanks for the advice guys! Bryan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pg_dump and server responsiveness
When we run pg_dump on our database, our web site becomes completely unresponsive. I thought pg_dump was runnable while the database was still being actively used? Anyway, I'm not entirely sure why, but here's what I'm seeing. pg_dump -v database_name | gzip > output_file 25% to 50% CPU usage (4 proc machine) Entire database is cached in memory, so the only I/O is what's being dumped to the output file Web site becomes unresponsive almost immediately Does pg_dump create table locks? It doesn't look like an I/O problem as far as I can tell... sdb: tables sdc: logs sdd: backup share total-cpu-usage -dsk/totaldsk/sdc-dsk/sdb-dsk/sdd-- --memory-usage- -net/total- swap--- usr sys idl wai hiq siq|_read _writ:_read _writ:_read _writ:_read _writ|_used _buff _cach _free|_recv _send|_used _free 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1276M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 083k: 0 0 : 067k: 0 0 | 525M 96k 14G 1272M|1176B 48k| 132k 7632M 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1269M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1265M| 564B 9824B| 132k 7632M 35 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1261M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 2048B: 0 0 : 0 0 : 0 0 | 525M 96k 14G 1258M| 140B 556B| 132k 7632M 36 4 49 10 0 1| 060M: 0 2048B: 0 184k: 0 60M| 535M 96k 14G 1245M|6827B 8682B| 132k 7632M 48 4 39 8 0 1| 046M: 0 168k: 0 0 : 0 46M| 542M 96k 14G 1234M| 652k 119M| 132k 7632M 38 2 52 7 0 0| 015M: 0 608k: 0 0 : 0 14M| 543M 96k 14G 1230M| 56k 24k| 132k 7632M Thanks, Bryan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump and server responsiveness
Sorry about the formatting, here's the dump as a text file. Thanks, Bryan On Dec 5, 2007 10:05 AM, Bryan Murphy <[EMAIL PROTECTED]> wrote: > When we run pg_dump on our database, our web site becomes completely > unresponsive. I thought pg_dump was runnable while the database was > still being actively used? Anyway, I'm not entirely sure why, but > here's what I'm seeing. > > pg_dump -v database_name | gzip > output_file > 25% to 50% CPU usage (4 proc machine) > Entire database is cached in memory, so the only I/O is what's being > dumped to the output file > Web site becomes unresponsive almost immediately total-cpu-usage -dsk/totaldsk/sdc-dsk/sdb-dsk/sdd-- --memory-usage- -net/total- swap--- usr sys idl wai hiq siq|_read _writ:_read _writ:_read _writ:_read _writ|_used _buff _cach _free|_recv _send|_used _free 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1276M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 083k: 0 0 : 067k: 0 0 | 525M 96k 14G 1272M|1176B 48k| 132k 7632M 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1269M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1265M| 564B 9824B| 132k 7632M 35 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1261M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 2048B: 0 0 : 0 0 : 0 0 | 525M 96k 14G 1258M| 140B 556B| 132k 7632M 36 4 49 10 0 1| 060M: 0 2048B: 0 184k: 060M| 535M 96k 14G 1245M|6827B 8682B| 132k 7632M 48 4 39 8 0 1| 046M: 0 168k: 0 0 : 046M| 542M 96k 14G 1234M| 652k 119M| 132k 7632M 38 2 52 7 0 0| 015M: 0 608k: 0 0 : 014M| 543M 96k 14G 1230M| 56k 24k| 132k 7632M ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_dump and server responsiveness
On Dec 5, 2007 10:14 AM, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Pg_dump uses Access Share if I recall. You can operate normally while > running pg_dump. I am having a hard time parsing that. Could you instead > go over to pgsql.privatepaste.com and send back a paste link? http://pgsql.privatepaste.com/5ako244Xe5 Sorry about that. Google tricked me into thinking it would format properly. :) Bryan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump and server responsiveness
On Dec 5, 2007 9:49 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Only access-share locks, but that could still be an issue if anything in > your system likes to take exclusive locks. Have you looked into > pg_locks to see if anything's getting blocked? > > pg_dump is entirely capable of causing an unpleasant amount of I/O > load, but that shouldn't result in "complete unresponsiveness", > and anyway your iostat output doesn't look like you're saturated... It does appear to be lock contention. I took a closer look this morning, and I noticed our web site was consistently locking up on a particular table, and there were a number of exclusive locks. I started eliminating various jobs, and found the one that essentially rewrites that particular table every 5 minutes to be the culprit (create new table, drop old table, rename new table). Is there a better way we can do this so that we won't causes lock contention during a dump? I can disable the process, but if the backup takes an hour that's an hour where all the data in this table is stale. I believe we chose to do it this way, because we wanted to minimize the amount of time the table wasn't available, which is why we didn't use a truncate based strategy. Thanks, Bryan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump and server responsiveness
On Dec 6, 2007 10:09 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Why dump such a table at all? It evidently doesn't contain any > data you need to preserve ... > > I forget which version you are running, but 8.2 pg_dump has an > --exclude-table switch which'd work peachy for this. I did not know about that option but it sounds like it will get the job done. This is our last database running 8.1.9, so even if it doesn't support that, I plan on migrating it to 8.2 soon anyway. Thanks, Bryan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] full text index and most frequently used words
I'm a bit of a novice writing tsearch2 queries, so forgive me if this is a basic question. We have a table with 2million+ records which has a considerable amount of text content. Some search terms (such as comedy, new, news, music, etc.) cause a significant performance hit on our web site. There are simply too many records in the table, and the ranking function takes too long to rank them all. We've partially solved this problem by manually identifying non-performant search queries and pre-caching the results (think materialized view). However, this process is starting to be become a burden, and we can't properly anticipate what our community is going to be searching for in the future. What I'd like to know is if there is an easy to way to use the full text index to generate a list of the most common words. I could write this code manually, but I'm hoping there's a better (simpler) way. Thanks, Bryan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Backup Strategy Second Opinion
Hey guys, we just moved our system to Amazon's EC2 service. I'm a bit paranoid about backups, and this environment is very different than our previous environment. I was hoping you guys could point out any major flaws in our backup strategy that I may have missed. A few assumptions: 1. It's OK if we lose a few seconds (or even minutes) of transactions should one of our primary databases crash. 2. It's unlikely we'll need to load a backup that's more than a few days old. Here's what we're currently doing: Primary database ships WAL files to S3. Snapshot primary database to tar file. Upload tar file to S3. Create secondary database from tar file on S3. Put secondary database into continuous recovery mode, pulling wal files from S3. Every night on secondary database: * shutdown postgres * unmount ebs volume that contains postgres data * create new snapshot of ebs volume * remount ebs volume * restart postgres I manually delete older log files and snapshots once I've verified that a newer snapshot can be brought up as an active database and have run a few tests on it. Other than that, we have some miscellaneous monitoring to keep track of the # of logs files in the pg_xlog directory and the amount of available disk space on all the servers. Ideally, if the # of log files starts to grow beyond a certain threshold, that indicates something went wrong with the log shipping and we'll investigate to see what the problem is. I think this is a pretty good strategy, but I've been so caught up in this I may not be seeing the forest through the trees so I thought I'd ask for a sanity check here. Thanks, Bryan -- 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 Strategy Second Opinion
On Sun, Feb 22, 2009 at 7:30 PM, Tim Uckun wrote: >> 1. It's OK if we lose a few seconds (or even minutes) of transactions >> should one of our primary databases crash. >> 2. It's unlikely we'll need to load a backup that's more than a few days >> old. > > How do you handle failover and falling back to the primary once it's up? We don't plan to fail back to the primary. Amazon is a very different beast, once a server is dead, we just toss it away. The secondary permanently becomes the primary and we create a new tertiary from scratch which then becomes a log shipped copy of the secondary. Bryan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schema Export/Import
Hey guys, we're changing the way we version our database from some old unsupported (and crappy) ruby migrations-like methodology to straight SQL scripts. We currently run CruiseControl.NET on a windows machine pointed to a test database server hosted on linux for our builds. At each build we attempt to drop the old database, create a new one, export the schema, then run the unit tests. The old way worked fine, but the new way is a little trickier. We have 169 migrations now, and I want to blow out the old migrations and reset 169 as our starting point. I dumped our current schema (pg_dump -s) to a sql file, and when I manually pipe it through psql, everything is fine. However, our build environment being a windows machine complicates this process. It's not so easy to just shell out and call psql. I will do it, if I have to, but I'm wondering if there's a better way. Unfortunately, I have not found a way to run this schema via Npgsql or PG Admin. What I want to know is if there is a way I can programmatically get the same behavior as piping the script through the psql command line. Both PG Admin and Npgsql seem to error out when running some commands (ex: CREATE TYPE gtsvector) where psql continues gracefully. There also seems to be a hard limit on the size of the command that can be passed in to PG Admin and Npgsql which we don't experience using the command line. Our schema definition is well beyond that hard-limit size, and I'm not looking forward to manually breaking up 20,000 lines of sql into separate files. :( Just fishing for ideas. Thanks, Bryan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Minimizing Recovery Time (wal replication)
I have two hot-spare databases that use wal archiving and continuous recovery mode. I want to minimize recovery time when we have to fail over to one of our hot spares. Right now, I'm seeing the following behavior which makes a quick recovery seem problematic: (1) hot spare applies 70 to 75 wal files (~1.1g) in 2 to 3 min period (2) hot spare pauses for 15 to 20 minutes, during this period pdflush consumes 99% IO (iotop). Dirty (from /proc/meminfo) spikes to ~760mb, remains at that level for the first 10 minutes, and then slowly ticks down to 0 for the second 10 minutes. (3) goto 1 My concern is that if the database has been in recovery mode for some time, even if it's caught up, if I go live sometime in (1) I can face a recovery time of upwards of 20 minutes. We've experienced delays during fail over in the past (not 20 minutes, but long enough to make me second guess what we are doing). I want to better understand what is going on so that I can determine what I can do (if anything) to minimize down time when we fail over to one of our hot spares. Here are my current settings: postgres (v8.3.7): shared_buffers = 2GB (15GB total) effective_cache_size = 12GB (15GB total) checkpoint_segments = 10 checkpoint_completion_target = 0.7 (other checkpoint/bgwriter settings left at default values) sysctl: kernel.shmmax = 2684354560 vm.dirty_background_ratio = 1 vm.dirty_ratio = 5 Thanks, Bryan -- 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] Minimizing Recovery Time (wal replication)
On Thu, Apr 9, 2009 at 6:38 PM, Greg Smith wrote: > What does vmstat say about the bi/bo during this time period? It sounds > like the volume of random I/O produced by recovery is just backing up as > expected. Some quick math: I'll have to capture this, unfortunately I won't be able to do that until tomorrow. This machine I was looking at is already failed over and I'm currently creating a new snapshot. I won't have a new hot spare to replace it until the morning. > 15GB RAM * 5% dirty_ratio = 750MB ; there's where your measured 760MB > bottleneck is coming from. That was what I thought, good to have it confirmed by somebody else. > 750MB / 10 minutes = 1.25MB/s ; that's in the normal range for random writes > with a single disk Yes, this is an interesting problem I'm having, more on it below... > Therefore my bet is that "vmstat 1" will show bo~=1250 the whole time you're > waiting there, with matching figures from the iostat to the database disk > during that period. > > Basically your options here are: > > 1) Decrease the maximum possible segment backlog so you can never get this > far behind I understand conceptually what you are saying, but I don't know how to practically realize this. :) Do you mean lower checkpoint_segments? > 2) Increase the rate at which random I/O can be flushed to disk by either > a) Improving things with a [better] battery-backed controller disk cache > b) Stripe across more disks This is the problem that has been my nightmare for the past few months. It actually is an 8 drive raid 10, BUT, it's on virtualized infrastructure up in Amazon's cloud running on 8 EBS volumes. I've found performance to be... inconsistent at best. Sometimes it's great, sometimes it's not so great. We have a legacy database (~120gb) which grew in our old data center on very powerful hardware. We moved it up to Amazon's cloud a few months ago, and have been scrambling ever since. I wouldn't change what we're doing, the benefits so far have outweighed the pain, and we're actively working on the software to make better use of the cloud infrastructure (i.e. many smaller databases instead of one big database, lots of caching, the usual stuff). Unfortunately, that takes time and I'm trying to limp along as best I can with the legacy database until we can get everything migrated. So, to recap, I've raided up the volumes, thrown as much RAM and CPU at the process as is available and just can't seem to tease any more performance out. Thanks, Bryan -- 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] Minimizing Recovery Time (wal replication)
On Thu, Apr 9, 2009 at 7:33 PM, Greg Smith wrote: >>> 1) Decrease the maximum possible segment backlog so you can never get >>> this >>> far behind >> >> I understand conceptually what you are saying, but I don't know how to >> practically realize this. :) Do you mean lower checkpoint_segments? > > Theoretically, every time the archive_command writes a new segment out you > can immediately move that to your standby, and setup the standby to > regularly look for those and apply them as they come in. The fact that > you're getting so many of them queued up suggests there's something in that > path that isn't moving that pipeline along aggressively enough, without > knowing more about what you're doing it's hard to say where that is. This is our archiving command: #!/bin/bash echo "archiving $2.bz2" bzip2 -k -9 -c "$1" > "/srv/pg_logs/archive/$2.bz2.tmp" || exit $? mv "/srv/pg_logs/archive/$2.bz2.tmp" "/srv/pg_logs/archive/$2.bz2" || exit $? scp "/srv/pg_logs/archive/$2.bz2" "w.x.y.z:/srv/logs/$2.bz2.tmp" || exit $? ssh w.x.y.z "mv /srv/logs/$2.bz2.tmp /srv/logs/$2.bz2" || exit $? rm "/srv/pg_logs/archive/$2.bz2" || exit $? And this is our restoring command: #!/bin/bash if [ "$1" == "" ] || [ "$2" == "" ]; then echo "dbrestore [source] [destination]" exit 1 fi echo "`date`: restoring $1" while true do if [ -f "$1.bz2" ]; then echo "`date`: restore $1.bz2 -> $2" bunzip2 -d -c "$1.bz2" > "$2.tmp" mv "$2.tmp" "$2" exit 0 fi if [[ "$1" =~ ".history" ]]; then echo "`date`: skipping $1" exit 1 fi if [ -f "/tmp/golive" ]; then echo "`date`: going live" rm -f "/tmp/golive" exit 2 fi sleep 5s done Essentially, what we do is bzip2 the file, scp it to the backup server, and then ssh rename it. The bzip2 is legacy from when we were uploading to Amazon via the public internet and can go away now. The rename can happen in the restore script, and is something I probably should change anyway, one less thing for the master database to do. We create file system snapshots of the hot spares, and I periodically purge the old log files after I've verified that we can bring the most recent snapshot live. We've used NFS in the past, but we're currently investigating other distribution alternatives (primarily londiste and pgpool2). We've used slony in the past, but find it introduces too much administrative overhead and is too brittle for our tastes. Thanks again! Bryan -- 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] Minimizing Recovery Time (wal replication)
On Sun, Apr 12, 2009 at 5:52 AM, Simon Riggs wrote: > The database is performing too frequent restartpoints. > > This has been optimised in PostgreSQL 8.4 by the addition of the > bgwriter running during recovery. This will mean that your hot spare > will not pause while waiting for restartpoint to complete, so will be > significantly faster. 8.4 is already looking like it's going to be a great upgrade for us, this would be another win. Thanks, Bryan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Having trouble restoring our backups
Hey guys, I'm having difficulty restoring some of our backups. Luckily, I'm only trying to do this to bring up a copy of our database for testing purposes, but this still has me freaked out because it means we currently have no valid backups and are only running with a single warm spare. Our primary database is on a RAID-10 that can't take snapshots and is very overworked, so we ship our wal files to a warm standby server. Every day or two I log in to the warm standby and run the following commands: 1. xfs_freeze -f /srv (this is where the entire postgres tree is mounted, no funny business with symlinks) 2. * take file system snapshot, wait about 30 seconds for snapshot to start running * 3. xfs_freeze -u /srv I don't exactly know how the snapshotting works (it's an Amazon EBS volume), so I don't know if I should wait until the snapshotting is 100% complete before I unfreeze the volume. This whole process can easily take 30 minutes to an hour, so I am also concerned that if I wait that long to unfreeze the volume I may cause an excessive backlog of wal files that are not getting applied to the warm spare. Now, when I try to restore one of these snapshots, I do the following: 1. create new share from snapshot 2. mount new share in new Linux instance 3. start postgres, verify that it's running and is still in recovery mode 4. touch my go live file and bring the database up I've done this successfully in the past. Today, however, I'm running into this problem when I try to run some queries: ERROR: could not access status of transaction 237546265 DETAIL: Could not open file "pg_clog/00E2": No such file or directory. I tried creating the missing files last night using dd, and I was able to get the database to a point where I was able to run queries against it, however it was missing data that should have been there. I tried again this morning with a different snapshot and I've run into the same problem again. What am I doing wrong? FYI, we're running 8.3.7. Thanks, Bryan
Re: [GENERAL] Having trouble restoring our backups
On Fri, Jun 12, 2009 at 10:48 AM, Alan Hodgson wrote: > On Friday 12 June 2009, Bryan Murphy wrote: > > What am I doing wrong? FYI, we're running 8.3.7. > > See the documentation on PITR backups for how to do this correctly. > I've read through the PITR documentation many times. I do not see anything that sheds light on what I'm doing wrong, and I've restored older backups successfully many times in the past few months using this technique. I have no explanation for why all of a sudden my last few backups are not restoring properly and we've not changed anything on our database setup recently. I'm currently creating a full backup of our primary database and will build a second warm spare with that, but the additional pressure this puts on our system is not acceptable as a long term backup solution. Bryan
Re: [GENERAL] Having trouble restoring our backups
On Fri, Jun 12, 2009 at 11:08 AM, Bryan Murphy wrote: > I've read through the PITR documentation many times. I do not see anything > that sheds light on what I'm doing wrong, and I've restored older backups > successfully many times in the past few months using this technique. I have > no explanation for why all of a sudden my last few backups are not restoring > properly and we've not changed anything on our database setup recently. > > I'm currently creating a full backup of our primary database and will build > a second warm spare with that, but the additional pressure this puts on our > system is not acceptable as a long term backup solution. > FYI, for future reference for anybody else who runs into this problem, it appears we somehow lost the pg_clog files during the last time we took a full snapshot of our primary database. Our PITR spare was happily recovering wal files, but when I tried to bring it up it was missing the pg_clogs and it's literally been weeks since I last tried to do this (stupid on my part). We appear to have repaired our PITR based backup by copying the missing pg_clog files from our production database which thankfully still had them. I do not know how they got dropped from the last snapshot we took, but we'll be looking into our hot-spare building process to see what we can do to prevent this from happening again. Thanks, Bryan
[GENERAL] Failover, Wal Logging, and Multiple Spares
Assuming we are running a Postgres instance that is shipping log files to 2 or more warm spares, is there a way I can fail over to one of the spares, and have the second spare start receiving updates from the new master without missing a beat? I can live with losing the old master, and at least at the moment it would be a controlled failover, but I would like to to know if it's possible during an uncontrolled failover as well (catastrophic hardware failure). Right now, we have just that setup, but every time I've failed over to the new master, we've had to rebuild our spares from scratch and unfortunately this is a multi-hour long process. We can't afford the risk of not having a warm spare for that length of time. We're planning to move entirely to a slony cluster, but I'd like to fail over to a more powerful machine before we begin the slony migration as the current server is already overloaded. Thanks, Bryan
[GENERAL] ERROR: attempted to delete invisible tuple
We had a hardware failure last week and had to switch over to our spare. Unfortunately, at some point we managed to get some data corruption. I've been going through the database table by table, record by record, trying to find the problems and fix them. This one has me stumped. We have one table with about 13 million records in it. I've identified 82 bad records. When I try to query for the records, we get the following: ERROR: missing chunk number 0 for toast value 25692661 in pg_toast_25497233 That's fine. I've run into that in a few other tables and have just been deleting the offending records. However, in this particular table, when I try and delete the records, I get the following error message: ERROR: attempted to delete invisible tuple I'm at a loss what to do next. Thanks, Bryan
Re: [GENERAL] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 11:35 AM, Greg Stark wrote:
> On Mon, Aug 17, 2009 at 4:23 PM, Bryan Murphy
> wrote:
> > I've identified 82 bad records. When I try to query for the records,
> > we get the following:
> > ERROR: missing chunk number 0 for toast value 25692661 in
> pg_toast_25497233
> > That's fine. I've run into that in a few other tables and have just been
> > deleting the offending records. However, in this particular table, when
> I
> > try and delete the records, I get the following error message:
> > ERROR: attempted to delete invisible tuple
> > I'm at a loss what to do next.
>
> Can you select xmin,xmax,ctid from the bad tuples?
>
> And also the output of pg_resetxlog -n. I suspect you may have failed
> to restore properly and have tuples from the "future" in your
> database. Perhaps you're missing at the last WAL file in your
> recovery?
>
> If that's the case you may be able to get out of it with pg_resetxlog
> and then cleaning up but it would be good to see the data first before
> changing things.
Here's the xmin/xmax/ctid for three problematic records:
prodpublic=# select xmin,xmax,ctid from items_extended where id in
('34537ed90d7546d78f2c172fc8eed687', '3e1d99b7124742b7aaf2f869f7637b0e',
'499b464f141a48619c5ce0475cbe9150');
xmin| xmax|ctid
---+---+
262232659 | 308810030 | (689496,3)
262629744 | 308810034 | (692765,1)
262643188 | 308810035 | (692920,9)
(3 rows)
I have not yet run pg_resetxlog.
Thanks,
Bryan
Re: [GENERAL] ERROR: attempted to delete invisible tuple
Could I run pg_resetxlog on a warm spare? Would that give the same result? Unfortunately, this is our production system and I simply cannot bring it down at the moment to run pg_resetxlog. Bryan On Mon, Aug 17, 2009 at 11:35 AM, Greg Stark wrote: > On Mon, Aug 17, 2009 at 4:23 PM, Bryan Murphy > wrote: > > I've identified 82 bad records. When I try to query for the records, > > we get the following: > > ERROR: missing chunk number 0 for toast value 25692661 in > pg_toast_25497233 > > That's fine. I've run into that in a few other tables and have just been > > deleting the offending records. However, in this particular table, when > I > > try and delete the records, I get the following error message: > > ERROR: attempted to delete invisible tuple > > I'm at a loss what to do next. > > Can you select xmin,xmax,ctid from the bad tuples? > > And also the output of pg_resetxlog -n. I suspect you may have failed > to restore properly and have tuples from the "future" in your > database. Perhaps you're missing at the last WAL file in your > recovery? > > If that's the case you may be able to get out of it with pg_resetxlog > and then cleaning up but it would be good to see the data first before > changing things. > > -- > greg > http://mit.edu/~gsstark/resume.pdf >
Re: [GENERAL] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 12:17 PM, Tom Lane wrote:
> Bryan Murphy writes:
> > Here's the xmin/xmax/ctid for three problematic records:
>
> > prodpublic=# select xmin,xmax,ctid from items_extended where id in
> > ('34537ed90d7546d78f2c172fc8eed687', '3e1d99b7124742b7aaf2f869f7637b0e',
> > '499b464f141a48619c5ce0475cbe9150');
> >xmin| xmax|ctid
> > ---+---+
> > 262232659 | 308810030 | (689496,3)
> > 262629744 | 308810034 | (692765,1)
> > 262643188 | 308810035 | (692920,9)
> > (3 rows)
>
> Hm, what's your current XID counter? (pg_controldata would give an
> approximate answer.) I'm wondering if the xmax's are marked committed
> but are in the future ...
>
>regards, tom lane
postg...@domu-12-31-36-00-38-c1:~$
/usr/lib/postgresql/8.3/bin/pg_controldata /srv/pg_data/8.3/main/
pg_control version number:833
Catalog version number: 200711281
Database system identifier: 5184368167085382525
Database cluster state: in production
pg_control last modified: Mon 17 Aug 2009 05:16:50 PM UTC
Latest checkpoint location: B4A/45E0DE38
Prior checkpoint location:B4A/3690AC00
Latest checkpoint's REDO location:B4A/36929DF0
Latest checkpoint's TimeLineID: 5
Latest checkpoint's NextXID: 0/308829887
Latest checkpoint's NextOID: 26030968
Latest checkpoint's NextMultiXactId: 5958225
Latest checkpoint's NextMultiOffset: 40968054
Time of latest checkpoint:Mon 17 Aug 2009 04:56:38 PM UTC
Minimum recovery ending location: A17/C6D9A98
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index: 32
Maximum size of a TOAST chunk:1996
Date/time type storage: 64-bit integers
Maximum length of locale name:128
LC_COLLATE: en_US.UTF-8
Thanks,
Bryan
Re: [GENERAL] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 12:41 PM, Tom Lane wrote: > Bryan Murphy writes: > > On Mon, Aug 17, 2009 at 12:17 PM, Tom Lane wrote: > >> Hm, what's your current XID counter? (pg_controldata would give an > >> approximate answer.) I'm wondering if the xmax's are marked committed > >> but are in the future ... > > > Latest checkpoint's NextXID: 0/308829887 > > Hmm, well, they're in the past *now*, but by less than 2 > transactions. You said this was a production DB so that might not > represent very much elapsed time. Does the DELETE still fail? > >regards, tom lane > Wow, how crazy is that. I was just able to delete those three records. I tried the remaining, and two were left that I could delete. Waited 30 seconds, tried again and now they're gone as well. As far as I'm aware, that's all the bad records in that table. The database went down last Weds. How could they have gotten that far into the future? Bryan
Re: [GENERAL] ERROR: attempted to delete invisible tuple
On Mon, Aug 17, 2009 at 4:02 PM, Greg Stark wrote: > For what it's worth at EDB I dealt with another case like this and I > imagine others have too. I think it's too easy to do things in the > wrong order or miss a step and end up with these kinds of problems. > > I would really like to know what happened here which caused the > problem. Do you have records of how you created the slave? When you > took the initial image, did you use a cold backup or a hot backup? Did > you use pg_start_backup()/pg_stop_backup()? I always use log shipping. The original snapshot was taken like the following (many months ago): start log shipping pg_start_backup() tar snapshot to nfs volume pg_stop_backup() restore snap shot on another machine startup in recovery mode The machine we restored to was not the original warm spare. It was built as a snapshot from the original warm spare. Every few weeks (or when paranoia sets in) I replace old spares with new spares to verify that the backups are working and bring the old spares online, verify them, then throw them away. xfs_freeze -f filesystem file system snapshot (EBS snapshot) xfs_freeze -u filesystem restore snapshot on another machine startup in recovery mode When you failed over was there anything special happening? Was it > because of a failure on the master? Was a vacuum full running? We never run vacuum full, but autovacuum is always turned on. At the time of failure, best I can tell is that the hard drive which contained the root file system and our swap partition was failing. Unfortunately, I was not at the office when this occurred and I'm the best one at diagnosing what's going on. Also, they're Amazon EC2 instances, so when it went dead, it went really dead. It's no longer available to us, and I had to quickly reclaim the EBS volumes it was using in order to get a new hot spare up and running. So, we basically lost everything but the console output of the original database, and of course, I can't find where I put that. I do remember it strongly indicated that we were facing imminent drive failure. When the slave came up do you have the log messages saying it was > starting recovery and when it was finishing recovery and starting > normal operations? This is tricky as well. We failed over to our spare, only to find out our load had grown so much the spare was no longer able to carry us the way it was configured. We had to quickly rebuild a new spare, and then shift over to that one. So, we basically shutdown the spare, shifted the volume over to the new machine, and then brought it back online. I then rebuilt a new warm spare from the new machine. Which leads me to the one big flaw in all of this, the log files were all going to the local drives and not the EBS volumes so I've lost them and am now kicking myself in the ass for it. Bryan
Re: [GENERAL] Failover, Wal Logging, and Multiple Spares
Ok, I've asked this a few times, but nobody ever responded. I think I finally got it though, could somebody confirm my logic? Basically, you setup a chain of servers, and when fails you replicate to the next link in the chain, like so: Master (A) --> Warm Standby (B) --> Warn Standby (C) --> etc. Master Fails, now becomes: Old Master (A) x> New Master (B) --> Warm Standby (C) And, of course, you might have an additional replication chain from Master (A) just in case you goof something up in the failover process, but that's the basic idea. Thanks, Bryan On Sun, Aug 16, 2009 at 9:35 PM, Bryan Murphy wrote: > Assuming we are running a Postgres instance that is shipping log files to 2 > or more warm spares, is there a way I can fail over to one of the spares, > and have the second spare start receiving updates from the new master > without missing a beat? I can live with losing the old master, and at least > at the moment it would be a controlled failover, but I would like to to know > if it's possible during an uncontrolled failover as well (catastrophic > hardware failure). > Right now, we have just that setup, but every time I've failed over to the > new master, we've had to rebuild our spares from scratch and unfortunately > this is a multi-hour long process. We can't afford the risk of not having a > warm spare for that length of time. We're planning to move entirely to a > slony cluster, but I'd like to fail over to a more powerful machine before > we begin the slony migration as the current server is already overloaded. > > Thanks, > Bryan >
Re: [GENERAL] Warm Standby Setup Documentation
On Mon, Mar 22, 2010 at 9:21 AM, Ogden wrote: > I have looked all over but could not find any detailed docs on setting up a > warm standby solution using PostgreSQL 8.4. I do know of > http://www.postgresql.org/docs/8.4/static/warm-standby.html but was > wondering if there was a more detailed document on this topic. > > Are people using this as a viable backup/hot spare solution? How has it > worked out? > > Thank you > > Ogden > We use it, it works pretty well, although it's a bit of a pain to set up the first time. We have two spares, one which is an equivalent sized machine we use for failover, and one which is a smaller machine that we use for worst-case-scenario file system snapshots/backups. The one thing you should be aware of is that when you fail over, your spare has no spares. I have not found a way around this problem yet. So, when you fail over, there is a window where you have no backups while you're building the new spares. This can be pretty nerve wracking if your database is like ours and it takes 3-6 hours to bring a new spare online from scratch. I was able to build out our solution reading the docs and asking questions on the mailing list. The information is in the docs, you just have to read it a few times for it to sink in. Bryan
Re: [GENERAL] Warm Standby Setup Documentation
On Fri, Mar 26, 2010 at 1:32 PM, Greg Smith wrote: > If there's another server around, you can have your archive_command on the > master ship to two systems, then use the second one as a way to jump-start > this whole process. After fail-over, just start shipping from the new > primary to that 3rd server, now the replacement standby, and sync any files > it doesn't have. Then switch it into recovery. Much faster than doing a > new base backup from the standby on larger systems. > > Every time I've tried to do this it's failed because the third server was looking for log files starting with 0006... but the secondary server (new master) is now shipping files starting with 0007... How do I get the third server to switch over to the higher numbered files? That's the part I was never able to overcome. I'd really like to fix this, because this has literally given me nightmares. :) Bryan
Re: [GENERAL] Postgresql on EC2/EBS in production?
On Tue, Apr 27, 2010 at 11:31 AM, Greg Smith wrote: > Nikhil G. Daddikar wrote: >> I was wondering if any of you are using (or tried to use) PG+EC2/EBS on a >> production system. Are any best-practices. Googling didn't help much. A few >> articles I came across scared me a bit. > > There have been a couple of reports of happy users: Been running a very busy 170+ gb OLTP postgres database on Amazon for 1.5 years now. I can't say I'm "happy" but I've made it work and still prefer it to running downtown to a colo at 3am when something goes wrong. > There are two main things to be wary of: > > 1) Physical I/O is not very good, thus how that first system used a RAID0. Let's be clear here, physical I/O is at times *terrible*. :) If you have a larger database, the EBS volumes are going to become a real bottleneck. Our primary database needs 8 EBS volumes in a RAID drive and we use slony to offload requests to two slave machines and it still can't really keep up. There's no way we could run this database on a single EBS volume. I also recommend you use RAID10, not RAID0. EBS volumes fail. More frequently, single volumes will experience *very long* periods of poor performance. The more drives you have in your raid, the more you'll smooth things out. However, there have been occasions where we've had to swap out a poor performing volume for a new one and rebuild the RAID to get things back up to speed. You can't do that with a RAID0 array. > 2) Reliability of EBS is terrible by database standards; I commented on this > a bit already at > http://archives.postgresql.org/pgsql-general/2009-06/msg00762.php The end > result is that you must be careful about how you back your data up, with a > continuous streaming backup via WAL shipping being the recommended approach. > I wouldn't deploy into this environment in a situation where losing a > minute or two of transactions in the case of a EC2/EBS failure would be > unacceptable, because that's something that's a bit more likely to hapen > here than on most database hardware. Agreed. We have three WAL-shipped spares. One streams our WAL files to a single EBS volume which we use for worst case scenario snapshot backups. The other two are exact replicas of our primary database (one in the west coast data center, and the other in an east coast data center) which we have for failover. If we ever have to worst-case-scenario restore from one of our EBS snapshots, we're down for six hours because we'll have to stream the data from our EBS snapshot back over to an EBS raid array. 170gb at 20mb/sec (if you're lucky) takes a LONG time. It takes 30 to 60 minutes for one of those snapshots to become "usable" once we create a drive from it, and then we still have to bring up the database and wait an agonizingly long time for hot data to stream back into memory. We had to fail over to one of our spares twice in the last 1.5 years. Not fun. Both times were due to instance failure. It's possible to run a larger database on EC2, but it takes a lot of work, careful planning and a thick skin. Bryan -- 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 on EC2/EBS in production?
On Tue, Apr 27, 2010 at 11:32 PM, Greg Smith wrote: > What do you mean by an instance failure here? The actual EC2 image getting > corrupted so that it won't boot anymore, or just the instance going down > badly? The instance going down, badly. The last time it happened, what logs I was able to recover after the fact seemed to indicate some kind of massive memory failure but I'll never know for sure. Bryan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 9.0 Hot Standby + Fail Over
We have a production database that contains data which is easily recreated at runtime. I'm considering upgrading this to 9.0 beta1 to get some experience with the new hot standby system on a server that is under medium to heavy load. Obviously, being a production database, it's inconvenient if this fails (but not catastrophic for us, which is why I want to try it). If partial updates are enabled and the master fails, I'd still like to fail over to one of the slaves. If the last log file was not shipped, but part of the queries for it were replicated, what happens? What happens if I have two slave servers (A and B) and I want to fail over to slave A and have it start shipping log files to slave B but B has more queries applied to it than A? I assume in this case I would instead want to fail over to B and ship to A. How would I know which server to fail over to? Thanks, Bryan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] missing chunk number 0 for toast value 25693266 in pg_toast_25497233
I'm running into this issue again: psql --version psql (PostgreSQL) 8.3.7 COPY items_extended TO '/dev/null'; ERROR: missing chunk number 0 for toast value 25693266 in pg_toast_25497233 Unfortunately, I do not know where these are coming from and I cannot replicate the data in at least one of my tables (which has 20 million records) because of this. I've already found 10 bad records. There are more. I have four tables with 20 million records (and a fifth which has even more) that may have this problem. Right now, checking every record one by one is going to take *DAYS* to complete, and I'm in the middle of adding additional nodes to our cluster because we are already stretched to the limit. I have a few options, such as trying to check batches of records and spinning up multiple checkers in parallel looking at different subsets of the data on wal shipped spares (assuming the wal shipped spares would suffer the same problem, which is a big assumption), but this is a lot of effort to get going. Help! Thanks, Bryan -- 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] missing chunk number 0 for toast value 25693266 in pg_toast_25497233
On Fri, May 7, 2010 at 9:02 AM, Magnus Hagander wrote: > Try doing a binary search with LIMIT. E.g., if you have 20M reecords, > do a SELECT * FROM ... LIMIT 10M. (throw away the results) If that > broke, check the upper half, if not, check the lower one (with > OFFSET). > > If you have a serial primary key or something, you can use WHERE on it > which will likely be a lot faster than using LIMIT, but the same idea > applies - do a binary search. Should take a lot less than days, and is > reasonably easy to script. That's my next step if I can't find a quicker/simpler method. My math tells me that my current script is going to take 24 days to test every record. Obviously, there are ways I can speed that up if I have no choice but I'm hoping for a simpler solution. I'd prefer to run a COPY TABLE like command and have it skip the bad records. Thanks, Bryan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
I can't get md5 authentication working with postgres 9rc1 and pgpool-II 3.0. I see references to "pool_passwd" in the pgpool documentation, but I see nothing indicating *where* this file should exist and how pgpool finds it. I've set my accounts up in pcp.conf, however, I do not believe this is what is expected. When I try to access the database with md5 turned on, I see the following error in my pgpool debug log: read_password_packet_password: failed to read password packet "p" I can get everything working with trust. postgres log: *LOG: unexpected EOF on client connection* LOG: disconnection: session time: 0:00:00.007 user=test database=test host=10.201.121.204 port=42812 psql output: psql -h 127.0.0.1 -p test test Password for user test: *psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. * pgpool debug log: 2010-09-14 21:38:51 DEBUG: pid 6633: I am 6633 accept fd 6 2010-09-14 21:38:51 LOG: pid 6633: connection received: host=127.0.0.1 port=48336 2010-09-14 21:38:51 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database: test user: test 2010-09-14 21:38:51 DEBUG: pid 6633: new_connection: connecting 0 backend 2010-09-14 21:38:51 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support is not available 2010-09-14 21:38:51 DEBUG: pid 6633: pool_read_message_length: slot: 0 length: 12 2010-09-14 21:38:51 DEBUG: pid 6633: trying md5 authentication 2010-09-14 21:38:51 DEBUG: pid 6633: DB node id: 0 salt: d3da5234 *2010-09-14 21:38:51 DEBUG: pid 6633: read_password_packet_password: failed to read password packet "p" 2010-09-14 21:38:51 ERROR: pid 6633: do_md5: read_password_packet failed *2010-09-14 21:38:51 DEBUG: pid 6633: do_md5failed in slot 0 2010-09-14 21:38:53 DEBUG: pid 6633: I am 6633 accept fd 6 2010-09-14 21:38:53 LOG: pid 6633: connection received: host=127.0.0.1 port=48338 2010-09-14 21:38:53 DEBUG: pid 6633: Protocol Major: 3 Minor: 0 database: test user: test 2010-09-14 21:38:53 DEBUG: pid 6633: new_connection: connecting 0 backend 2010-09-14 21:38:53 DEBUG: pid 6633: pool_ssl: SSL requested but SSL support is not available 2010-09-14 21:38:53 DEBUG: pid 6633: pool_read_message_length: slot: 0 length: 12 2010-09-14 21:38:53 DEBUG: pid 6633: trying md5 authentication 2010-09-14 21:38:53 DEBUG: pid 6633: DB node id: 0 salt: 855592aa 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler called 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: call wait3 *2010-09-14 21:38:53 ERROR: pid 6603: Child process 6633 was terminated by segmentation fault 2010-09-14 21:38:53 DEBUG: pid 6603: child 6633 exits with status 11 by signal 11 *2010-09-14 21:38:53 DEBUG: pid 6603: fork a new child pid 6671 2010-09-14 21:38:53 DEBUG: pid 6603: reap_handler: normally exited pg_hba.conf: local all all trust hostall all 127.0.0.1/32trust hostall all ::1/128 trust hostall all 0.0.0.0/0 md5 pool_hba.conf: local all all trust host all all 0.0.0.0/0 md5 pgpool.conf: listen_addresses = '*' port = pcp_port = 9898 socket_dir = '/srv/pgpool/run' pcp_socket_dir = '/srv/pgpool/run' backend_socket_dir = '/srv/pgpool/run' pcp_timeout = 10 num_init_children = 64 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/srv/pgpool/log' pid_file_name = '/srv/pgpool/run/pgpool.pid' replication_mode = false load_balance_mode = false replication_stop_on_mismatch = false failover_if_affected_tuples_mismatch = false replicate_select = false reset_query_list = 'ABORT; DISCARD ALL' white_function_list = '' black_function_list = 'nextval,setval' print_timestamp = true master_slave_mode = false master_slave_sub_mode = 'slony' delay_threshold = 0 log_standby_delay = 'none' connection_cache = true health_check_timeout = 20 health_check_period = 0 health_check_user = 'nobody' failover_command = '' failback_command = '' fail_over_on_backend_error = true insert_lock = true ignore_leading_white_space = true log_statement = false log_per_node_statement = false log_connections = true log_hostname = false parallel_mode = false enable_query_cache = false pgpool2_hostname = '' system_db_hostname = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' backend_hostname0 = '10.215.74.98' backend_port0 = 5432 backend_weight0 = 1 *enable_pool_hba = true *recovery_user = 'nobody' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 client_idle_limit_in_recovery = 0 lobj_lock_table = '' ssl = false debug_level = 100
Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Tue, Sep 14, 2010 at 6:55 PM, Tatsuo Ishii wrote: > Sorry for not enough description about pool_passwd. It's located under > the same directory as pgpool.conf. So the default is > /usr/local/etc/pool_passwd. > > You need to create /usr/local/etc/pool_passwd if the uid to run pgpool > server does not have the right to create a new file under > /usr/local/etc, you need to create an empty /usr/local/etc/pool_passwd > owned by the same uid as pgpool. After that you should be able to > register your password as decribed in the doc. > > Authentication / Access Controls > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > Thanks, I should have thought to try that location. :) I put the pool_passwd file in the expected location (custom build, /opt/postgres/etc/pool_passwd and /etc/pool_passwd) and reran my test. I'm still having the same connection problem. I think it would be good to add an additional command line parameter to pgpool to allow overriding the location of that file (the same way the location of pcp.conf, pgpool.conf, and pgpool_hba.conf can be overriden). direct connection: psql -h 10.192.165.125 -p 5432 template1 test Password for user test: psql (9.0rc1) Type "help" for help. template1=# proxied connection: psql -h 127.0.0.1 -p template1 test Password for user test: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. pgpool log: 2010-09-15 15:48:36 LOG: pid 2324: connection received: host=127.0.0.1 port=46999 2010-09-15 15:48:36 DEBUG: pid 2324: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-15 15:48:36 DEBUG: pid 2324: new_connection: connecting 0 backend 2010-09-15 15:48:36 DEBUG: pid 2324: pool_ssl: SSL requested but SSL support is not available 2010-09-15 15:48:36 DEBUG: pid 2324: pool_read_message_length: slot: 0 length: 12 2010-09-15 15:48:36 DEBUG: pid 2324: trying md5 authentication 2010-09-15 15:48:36 DEBUG: pid 2324: DB node id: 0 salt: 458c816a *2010-09-15 15:48:36 DEBUG: pid 2324: read_password_packet_password: failed to read password packet "p"* 2010-09-15 15:48:36 ERROR: pid 2324: do_md5: read_password_packet failed 2010-09-15 15:48:36 DEBUG: pid 2324: do_md5failed in slot 0 2010-09-15 15:48:38 DEBUG: pid 2323: I am 2323 accept fd 6 2010-09-15 15:48:38 LOG: pid 2323: connection received: host=127.0.0.1 port=47001 2010-09-15 15:48:38 DEBUG: pid 2323: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-15 15:48:38 DEBUG: pid 2323: new_connection: connecting 0 backend 2010-09-15 15:48:38 DEBUG: pid 2323: pool_ssl: SSL requested but SSL support is not available 2010-09-15 15:48:38 DEBUG: pid 2323: pool_read_message_length: slot: 0 length: 12 2010-09-15 15:48:38 DEBUG: pid 2323: trying md5 authentication 2010-09-15 15:48:38 DEBUG: pid 2323: DB node id: 0 salt: 6d1da26 2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler called 2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler: call wait3 2010-09-15 15:48:38 ERROR: pid 2260: Child process 2323 was terminated by segmentation fault 2010-09-15 15:48:38 DEBUG: pid 2260: child 2323 exits with status 11 by signal 11 2010-09-15 15:48:38 DEBUG: pid 2260: fork a new child pid 2328 2010-09-15 15:48:38 DEBUG: pid 2260: reap_handler: normally exited 2010-09-15 15:48:38 DEBUG: pid 2328: I am 2328 postgres log: LOG: invalid password packet size LOG: unexpected EOF on client connection psql --version psql (PostgreSQL) 9.0rc1 contains support for command-line editing pgpool --version pgpool-II version 3.0 (umiyameboshi)
Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Sun, Sep 19, 2010 at 11:31 PM, Tatsuo Ishii wrote: > Sorry for delay. I had a trip outside Japan. > No problem. > I found nasty bug with pgpool. Please try attached patches. > I tried the patch file and I still cannot connect. The only other difference is that I've already upgraded our images to Postgres 9.0.0 from rc1. Here is the pgpool debug log: 2010-09-20 19:43:19 DEBUG: pid 1329: I am 1329 accept fd 6 2010-09-20 19:43:19 LOG: pid 1329: connection received: host=127.0.0.1 port=39064 2010-09-20 19:43:19 DEBUG: pid 1329: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-20 19:43:19 DEBUG: pid 1329: new_connection: connecting 0 backend 2010-09-20 19:43:19 DEBUG: pid 1329: pool_ssl: SSL requested but SSL support is not available 2010-09-20 19:43:19 DEBUG: pid 1329: pool_read_message_length: slot: 0 length: 12 2010-09-20 19:43:19 DEBUG: pid 1329: pool_do_auth: auth kind:5 2010-09-20 19:43:19 DEBUG: pid 1329: trying md5 authentication 2010-09-20 19:43:19 DEBUG: pid 1329: DB node id: 0 salt: 89d02c38 2010-09-20 19:43:19 DEBUG: pid 1329: read_password_packet_password: failed to read password packet "p" 2010-09-20 19:43:19 ERROR: pid 1329: do_md5: read_password_packet failed 2010-09-20 19:43:19 DEBUG: pid 1329: do_md5failed in slot 0 2010-09-20 19:43:21 DEBUG: pid 1350: I am 1350 accept fd 6 2010-09-20 19:43:21 LOG: pid 1350: connection received: host=127.0.0.1 port=39066 2010-09-20 19:43:21 DEBUG: pid 1350: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-20 19:43:21 DEBUG: pid 1350: new_connection: connecting 0 backend 2010-09-20 19:43:21 DEBUG: pid 1350: pool_ssl: SSL requested but SSL support is not available 2010-09-20 19:43:21 DEBUG: pid 1350: pool_read_message_length: slot: 0 length: 12 2010-09-20 19:43:21 DEBUG: pid 1350: pool_do_auth: auth kind:5 2010-09-20 19:43:21 DEBUG: pid 1350: trying md5 authentication 2010-09-20 19:43:21 DEBUG: pid 1350: DB node id: 0 salt: 474a91ef 2010-09-20 19:43:21 DEBUG: pid 1350: send_password_packet: backend does not return R 2010-09-20 19:43:21 DEBUG: pid 1350: do_md5failed in slot 0
Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Mon, Sep 20, 2010 at 6:23 PM, Tatsuo Ishii wrote: > I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5 > auth. Your log seems to indicate that the password in pool_passwd and > the one in pg_shadow are not identical. Can you verify that? > The query result: > > select passwd from pg_shadow where usename = 'test'; > > must be identical the password in pool_passwd for user test. Ah, that was it! I'll double check how I'm generating the passwords in pool_passwd, but once I copied the md5 from pg_shadow to pool_passwd it started working. Many thanks for your help! Bryan
Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Tue, Sep 21, 2010 at 10:26 AM, Bryan Murphy wrote: > On Mon, Sep 20, 2010 at 6:23 PM, Tatsuo Ishii wrote: > >> I have used PostgreSQL 9.0 + pgpool-II 3.0 and they work fine with md5 >> auth. Your log seems to indicate that the password in pool_passwd and >> the one in pg_shadow are not identical. Can you verify that? >> The query result: >> >> select passwd from pg_shadow where usename = 'test'; >> >> must be identical the password in pool_passwd for user test. > > > Ah, that was it! I'll double check how I'm generating the passwords in > pool_passwd, but once I copied the md5 from pg_shadow to pool_passwd it > started working. > > Many thanks for your help! > > Bryan > I'm sorry, when I went back over to double check my steps I realized I ran the wrong command. I am *still* having the problem. It appears that the MD5 hashes now match, but it's still failing. I have postgres and pgpool installed in /opt/postgresql, but I have the same problem when I put pool_passwd in /etc and /opt/postgresql/etc. psql -h 127.0.0.1 -p template1 test Password for user test: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql -h 10.240.163.95 template1 test Password for user test: psql (9.0.0) Type "help" for help. template1=# select usename, passwd from pg_shadow where usename = 'test'; usename | passwd -+- test| md505a671c66aefea124cc08b76ea6d30bb (1 row) cat /opt/postgresql/etc/pool_passwd test:05a671c66aefea124cc08b76ea6d30bb cat /opt/postgresql/etc/pgpool.conf | grep hostname0 backend_hostname0 = '10.240.163.95' pgpool log: 2010-09-21 15:36:26 LOG: pid 4661: connection received: host=127.0.0.1 port=41418 2010-09-21 15:36:26 DEBUG: pid 4661: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-21 15:36:26 DEBUG: pid 4661: new_connection: connecting 0 backend 2010-09-21 15:36:26 DEBUG: pid 4661: pool_ssl: SSL requested but SSL support is not available 2010-09-21 15:36:26 DEBUG: pid 4661: pool_read_message_length: slot: 0 length: 12 2010-09-21 15:36:26 DEBUG: pid 4661: pool_do_auth: auth kind:5 2010-09-21 15:36:26 DEBUG: pid 4661: trying md5 authentication 2010-09-21 15:36:26 DEBUG: pid 4661: DB node id: 0 salt: fa1f213a 2010-09-21 15:36:26 DEBUG: pid 4661: read_password_packet_password: failed to read password packet "p" 2010-09-21 15:36:26 ERROR: pid 4661: do_md5: read_password_packet failed 2010-09-21 15:36:26 DEBUG: pid 4661: do_md5failed in slot 0 2010-09-21 15:36:27 DEBUG: pid 4717: I am 4717 accept fd 6 2010-09-21 15:36:27 LOG: pid 4717: connection received: host=127.0.0.1 port=41420 2010-09-21 15:36:27 DEBUG: pid 4717: Protocol Major: 3 Minor: 0 database: template1 user: test 2010-09-21 15:36:27 DEBUG: pid 4717: new_connection: connecting 0 backend 2010-09-21 15:36:27 DEBUG: pid 4717: pool_ssl: SSL requested but SSL support is not available 2010-09-21 15:36:27 DEBUG: pid 4717: pool_read_message_length: slot: 0 length: 12 2010-09-21 15:36:27 DEBUG: pid 4717: pool_do_auth: auth kind:5 2010-09-21 15:36:27 DEBUG: pid 4717: trying md5 authentication 2010-09-21 15:36:27 DEBUG: pid 4717: DB node id: 0 salt: 1c8e5961 2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler called 2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler: call wait3 2010-09-21 15:36:27 ERROR: pid 4654: Child process 4717 was terminated by segmentation fault 2010-09-21 15:36:27 DEBUG: pid 4654: child 4717 exits with status 11 by signal 11 2010-09-21 15:36:27 DEBUG: pid 4654: fork a new child pid 4722 2010-09-21 15:36:27 DEBUG: pid 4654: reap_handler: normally exited 2010-09-21 15:36:27 DEBUG: pid 4722: I am 4722
Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Tue, Sep 21, 2010 at 10:45 AM, Bryan Murphy wrote: > I'm sorry, when I went back over to double check my steps I realized I ran > the wrong command. I am *still* having the problem. It appears that the > MD5 hashes now match, but it's still failing. I have postgres and pgpool > installed in /opt/postgresql, but I have the same problem when I put > pool_passwd in /etc and /opt/postgresql/etc. > Here is a gdb backtrace from the child process when it sigsegs after I enter the correct password. I'm digging in a little deeper now and I thought this might be useful. (gdb) bt #0 0xefcbf24f in ?? () #1 0x08052ac5 in do_md5 (backend=0xefcbf24f, frontend=0x9dfc5c0, reauth=, protoMajor=3) at /usr/include/bits/string3.h:52 #2 0x08052e7b in pool_do_auth (frontend=0x9df97c0, cp=0x9df8fa8) at pool_auth.c:222 #3 0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143 #4 do_child (unix_fd=4, inet_fd=5) at child.c:293 #5 0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=) at main.c:1024 #6 0x0804ddef in main (argc=3, argv=0xbfab6754) at main.c:514 > >
Re: [GENERAL] pgpool-II 3.0 + postgres 9rc1 + md5 authentication not working
On Tue, Sep 21, 2010 at 8:08 PM, Tatsuo Ishii wrote: > Unfortunately the gdb backtrace does not show enough information > because of optimization, I guess. Can you take a backtrace with > optimization disabled binary? > > You can obtain this by editing Makefile around line 147. > > I edited configure and change every instance of -O2 to -O for postgres and pgpool and added --enable-debug. Here's the new backtrace: (gdb) bt #0 0xb75fc346 in ?? () from /lib/tls/i686/nosegneg/libc.so.6 #1 0x0805f342 in pool_flush (cp=0x3) at pool_stream.c:486 #2 0x08052e7b in pool_do_auth (frontend=0x946b778, cp=0x0) at pool_auth.c:222 #3 0x080509a9 in connect_backend (unix_fd=4, inet_fd=5) at child.c:1143 #4 do_child (unix_fd=4, inet_fd=5) at child.c:293 #5 0x0804bbdf in fork_a_child (unix_fd=4, inet_fd=5, id=) at main.c:1024 #6 0x0804ddef in main (argc=3, argv=0xbfaad2d4) at main.c:514
