[GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb
$ initdb -D data-default The files belonging to this database system will be owned by user "pgsql". This user must also own the server process. The database cluster will be initialized with locale "C". The default database encoding has accordingly been set to "SQL_ASCII". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory data-default ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 400kB selecting dynamic shared memory implementation ... posix creating configuration files ... ok creating template1 database in data-default/base/1 ... FATAL: could not create semaphores: Invalid argument DETAIL: Failed system call was semget(2, 17, 03600). child process exited with exit code 1 initdb: removing contents of data directory "data-default" $ $ sysctl -a|grep semm kern.ipc.semmsl: 512 kern.ipc.semmnu: 256 kern.ipc.semmns: 512 kern.ipc.semmni: 256 The system is running 9.4 just fine and the kernel configuration requirements shouldn't have changed for semaphores should they? -- Karl Denninger k...@denninger.net <mailto:k...@denninger.net> /The Market Ticker/ /[S/MIME encrypted email preferred]/ smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb
On 2/4/2016 12:47, Tom Lane wrote: > I wrote: >> Karl Denninger <k...@denninger.net> writes: >>> $ initdb -D data-default >>> ... >>> creating template1 database in data-default/base/1 ... FATAL: could not >>> create semaphores: Invalid argument >>> DETAIL: Failed system call was semget(2, 17, 03600). >> Hmm. On my Linux box, "man semget" says EINVAL means >>EINVAL nsems is less than 0 or greater than the limit on the number >> of >> semaphores per semaphore set (SEMMSL), or a semaphore set >> corre- >> sponding to key already exists, and nsems is larger than >> the >> number of semaphores in that set. >> which agrees with the POSIX spec. Is FreeBSD the same? > BTW, looking at the code, I see that during initdb we would have tried > semaphore key 1 before 2. So presumably, on key 1 we got an error code > that we recognized as meaning "semaphore set already exists", but then on > key 2 we got EINVAL instead. That makes this even more curious. I'd > be interested to see what "ipcs -s" says, if you have that command. > (You might need to run it as root to be sure it will show all sempaphores.) > > regards, tom lane There was indeed a "2" key out by the web server process; I shut it down and cleared it and the upgrade is now running Also filed a kernel bug with the FreeBSD folks against 10.2-STABLE as the man page says you should have gotten back EEXIST. -- Karl Denninger k...@denninger.net <mailto:k...@denninger.net> /The Market Ticker/ /[S/MIME encrypted email preferred]/ smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Upgrade from 9.4 -> 9.5, FreeBSD 10.2-STABLE, fails on initdb
On 2/4/2016 12:28, Tom Lane wrote: > Karl Denninger <k...@denninger.net> writes: >> $ initdb -D data-default >> ... >> creating template1 database in data-default/base/1 ... FATAL: could not >> create semaphores: Invalid argument >> DETAIL: Failed system call was semget(2, 17, 03600). > Hmm. On my Linux box, "man semget" says EINVAL means > >EINVAL nsems is less than 0 or greater than the limit on the number of > semaphores per semaphore set (SEMMSL), or a semaphore set corre- > sponding to key already exists, and nsems is larger than the > number of semaphores in that set. > > which agrees with the POSIX spec. Is FreeBSD the same? > > Proceeding on the assumption that it is ... > > 17 is the same nsems value we've been using for donkey's years, so the > SEMMSL aspect of this seems unlikely to apply; what presumably is > happening is a collision with an existing semaphore's key. Our code is > prepared for that, but it expects a different error code in such cases, > either EEXIST or EACCES: > > /* > * Fail quietly if error indicates a collision with existing set. One > * would expect EEXIST, given that we said IPC_EXCL, but perhaps we > * could get a permission violation instead? Also, EIDRM might occur > * if an old set is slated for destruction but not gone yet. > */ > > It sounds like your kernel is returning EINVAL in preference to any of > those codes, which would be pretty broken. I do not want to make our code > treat EINVAL as meaning we should retry with a different key, because if > the problem is indeed the SEMMSL limit, we'd be in an infinite loop. > > You can probably get past this for the moment if you can remove the > semaphore set with key 2, but I'd advise filing a FreeBSD kernel > bug about their choice of errno. > > regards, tom lane That sounds like it well may be the problem s655642 --rw-rw-rw- www www www www 3 12:29:14 7:56:04 Oh look, the web server process has a semaphore set out with a "2" key I've filed a bug report. Thanks. -- Karl Denninger k...@denninger.net <mailto:k...@denninger.net> /The Market Ticker/ /[S/MIME encrypted email preferred]/ smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] streaming replication not working
On 9/23/2013 9:30 PM, Ray Stell wrote: On Sep 23, 2013, at 4:47 PM, John DeSoi wrote: You mean on the primary, right? right Yes, I have one there. But even if I did not, I would expect to see a connection error in the log on the standby. No error or any indication the streaming replication process is running on the standby. you're right, I was firing from the hip. sure enough, it does toss an error: 2013-09-23 18:00:01 EDT,0,authentication FATAL: 28000: no pg_hba.conf entry for replication connection from host xxx, user repuser, SSL off I'd guess a firewall issue? What happens with telnet primary_host port maybe use tcpdump to see what's happening with the traffic? No, there is a missing line in pg_hba.conf that should look something like this: hostreplication repuserxxx trust (where xxx is the hostname) See the pg_hba.conf file for more examples. Note that replication is a special database tag and a replicating connection must have one of these defined as all does not match it. You can use host, hostssl or hostnossl; trust means that no password is demanded and for obvious reasons should NOT be used for other than a local connection that can be trusted implicitly. I prefer not to use that method for other than local socket connections and then only on a machine where nobody signs in that is untrusted (e.g. only admins are permitted general access.) If you are connecting over an insecure channel or untrusted users are on the machine then consider SSL to encrypt the traffic and either use md5 for the password or use a certificate. You can reload the file without restarting postgres with pg_ctl -D data-directory reload (where data-directory is wherever the data directory that has the pg_hba.conf file -- and the rest of the base of the data store -- is) -- Karl Denninger k...@denninger.net /Cuda Systems LLC/ smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
On 9/12/2013 11:11 AM, Patrick Dung wrote: While reading some manual of PostgreSQL and MySQL (eg. http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html). I have found that MySQL has stated many incompatibilities and know issues (a long list) in the MySQL version 5.0, 5.1, 5.5, 5.6 and 5.7. For PostgreSQL, it seems I can't find the list (it just say see the Appendix E / release notes). I think it is a plus for PostgreSQL if it has few incompatibilities between major versions. By the way, for in-place major version upgrade (not dumping DB and import again), MySQL is doing a better job in here. Please share your thought, thanks. pg_upgrade will do an in-place upgrade if you wish. It is somewhat risky if not done using a COPY (it can either copy or not, as you wish) but it's considerably faster than a dump/restore and is in-place. I use it regularly. -- Karl Denninger k...@denninger.net /Cuda Systems LLC/ smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Success stories of PostgreSQL implementations in different companies
On 5/24/2013 10:49 AM, Merlin Moncure wrote: On Fri, May 24, 2013 at 10:15 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, May 24, 2013 at 9:10 AM, Bèrto ëd Sèra berto.d.s...@gmail.com wrote: The Greater London Authority is also ditching Oracle in favour of PG. I consulted them while they kick started their transition and the first new PG/PostGIS only project is already delivered. The number of companies ditching Oracle is probably much larger than it seems, giving the dynamics in salaries. The average PG based salary goes up steady, while working with Oracle is going down pretty quick. At least, so it would look from the UK. An Oracle DBA in average is currently offered some 15% less than a PG dba. Where I currently work we've been looking for a qualified production postgres DBA. They (we?) are hard to come by. This. The major barrier to postgres adoption is accessibility of talent. OTOH, postgres tends to attract the best and smartest developers and so the price premium is justified. This is not just bias speaking...I work on the hiring side and it's a frank analysis of the current state of affairs. Postgres is white hot. The database is competitive technically (better in some ways worse in others) vs the best of the commercial offerings but is evolving much more quickly. merlin They/we are not THAT hard to come by. It's the common lament that customers have in a nice whorehouse. The price is too high. (You can easily pay me to quit doing what I'm doing now and do something else; the problem only rests in one place when it comes to enticing me to do so -- money. :-)) -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
Re: [GENERAL] Storing small image files
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: Take a look here first : http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com mailto:ach...@matrix.gatewaynet.com wrote: why not bytea? Hi Achilleas, Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. much more control, much more information, IMHO. In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, we have been storing everything binary in bytea's. There are downsides in both solutions, you just have to have good reasons to not use bytea. On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt To encode: write_conn = Postgresql communication channel in your software that is open to write to the table char*out; size_tout_length, badge_length; badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
Re: [GENERAL] Storing small image files
On 5/9/2013 11:12 AM, Karl Denninger wrote: On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: Take a look here first : http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com mailto:ach...@matrix.gatewaynet.com wrote: why not bytea? Hi Achilleas, Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. much more control, much more information, IMHO. In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, we have been storing everything binary in bytea's. There are downsides in both solutions, you just have to have good reasons to not use bytea. On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt To encode: write_conn = Postgresql communication channel in your software that is open to write to the table char*out; size_tout_length, badge_length; badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net /Cuda Systems LLC/ Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how would you know how long it is? :-) BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data. -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
Re: [GENERAL] Storing small image files
On 5/9/2013 11:34 AM, Alvaro Herrera wrote: Karl Denninger escribió: To encode: write_conn = Postgresql communication channel in your software that is open to write to the table char*out; size_tout_length, badge_length; badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. I think you're better off with PQexecParams() so that you don't have to encode the image at all; just load it in memory and use it as a parameter. Yeah, you can go that route too. -- Karl Denninger k...@denninger.net /Cuda Systems LLC/
Re: [GENERAL] Storing small image files
On 5/9/2013 12:08 PM, Nelson Green wrote: Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I? On Thu, May 9, 2013 at 11:21 AM, Karl Denninger k...@denninger.net mailto:k...@denninger.net wrote: On 5/9/2013 11:12 AM, Karl Denninger wrote: On 5/9/2013 10:51 AM, Achilleas Mantzios wrote: Take a look here first : http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html didnt try it myself tho. Most of the time people manipulate bytea's using a higher level programming lang. On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote: On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com mailto:ach...@matrix.gatewaynet.com wrote: why not bytea? Hi Achilleas, Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client. Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record? Thanks for your time. much more control, much more information, IMHO. In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0, we have been storing everything binary in bytea's. There are downsides in both solutions, you just have to have good reasons to not use bytea. On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote: Good morning list, I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that. The table structures: CREATE TABLE employee ( employee_idINTEGER NOT NULL, employee_lastname VARCHAR(35) NOT NULL, employee_firstname VARCHAR(35) NOT NULL, employee_miCHAR(1), PRIMARY KEY (employee_id) ); CREATE TABLE security_badge ( badge_number CHAR(10)NOT NULL, employee_idINTEGER NOT NULL REFERENCES employee(employee_id), badge_photo, PRIMARY KEY (badge_number) ); What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please? Thanks, Nelson - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt To encode: write_conn = Postgresql communication channel in your software that is open to write to the table char*out; size_tout_length, badge_length; badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */ out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, out_length); /* Convert */ That function allocates the required memory for the conversion. You now have an encoded string you can insert or update with. Once you use it in an insert or update function you then must PQfreemem(out) to release the memory that was allocated. To recover the data you do: PQresult *result; result = PQexec(write_conn, select badge_photo blah-blah-blah); out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */ out now contains the BINARY (decoded) photo data. When done with it you: PQfreemem(out) to release the memory that was allocated. That's the rough outline -- see here: http://www.postgresql.org/docs/current/static/libpq-exec.html -- Karl Denninger k...@denninger.net mailto:k...@denninger.net /Cuda Systems LLC/ Oops -- forgot the second parameter on the PQunescapebytea call :-) Yeah, that would be bad: size_t out_length; out = PQunescapeBytea(PQgetvalue(result, 0, 0), out_length); /* Get the returned piece of the tuple and convert it */ Otherwise, being binary data, how
Re: [GENERAL] PG V9 on NFS
On 2/11/2013 4:22 PM, Gauthier, Dave wrote: Can PG V9.1* support a DB that's on an NFS disk? I googled around, but nothing popped out. Also, would you happen to know the answer to this for MySQL v5.5*? Thanks in Advance. That would be (IMHO) very ill-advised. In the event of a disruption between the two systems you're virtually guaranteed to suffer data corruption which is (much worse) rather likely to go undetected. -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
[GENERAL] Running multiple instances off one set of binaries
Let's assume I want to run: 1. An instance of the database that is a replicated copy from another site. 2. A LOCAL instance that contains various things on the local machine that are not shared. Let's further assume all are to be 9.2 revs. Am I correct in that I can do this by simply initdb-ing the second instance with a different data directory structure, and when starting it do so with a different data directory structure? e.g. initdb -D data and initdb -D data2 And that as long as there are no collisions (E.g. port numbers) this works fine? -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
Re: [GENERAL] Monitoring streaming replication from standby on Windows
On 12/13/2012 7:36 PM, Yamen LA wrote: Hello, I would like to know how to check the status of the streaming replication from standby server on Windows. Apparently from the master I can use the pg table pg_stat_replication. This table is, however, empty on the standby since it contains information about WAL sender processes and not WAL receiver. pg_last_xlog_replay_location and pg_last_xlog_receive_location also continue to be valid even when the streaming replication is down, so they don't help in this case. From online tutorials and PostgreSQL wiki the only way I found is by checking the running processes for wal sender and wal receiver using ps command on Unix systems. The problem is that on Windows, all those processes carry the same name, postgresql.exe. I suppose there should be some parameter to get the db engine as it realizes when the streaming replication is down and it logs that in pg_log files, but I can't seem to find such a parameter. Thank you for your help. -Yamen What are you trying to determine? If it's whether the replication is caught up, I have a small C program that will do that and have posted it before (I can do that again if you'd like.) If it's whether it's up, that's a bit more complex, since you have to define up. For most purposes determining that the offset between the two is less than some value at which you alarm is sufficient, and if you then alarm if you can't reach the master and slave hosts, you then know if the machines are up from a standpoint of reachability on the network as well. -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
[GENERAL] pg_lesslog - status?
Does anyone know where this stands? I used this a while back in the 8.x days but had a serious bug problem with it -- which was later fixed. The latest checks I can find say it was updated for 9.0, but that leaves open the question as to whether it functions correctly with the current release. There's no status update on the pgfoundry page indicating activity or testing with the current releases. Thanks in advance. -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
Re: [GENERAL] Async replication: how to get an alert on failure
On 8/1/2012 5:29 PM, Edson Richter wrote: I have few PostgreSQL servers established with replication. Now, is there any way to receive an e-mail in case of error during synchronization? I mean, is there an script I can run (maybe every 5 minutes) that will check if replication is stolen, and send an e-mail in case of failure? Thanks, -- I wrote a small program that goes to each of the servers in the replication group and checks its status on the log, computes the difference, and if off by more than X bytes prints a notification. If this is run from the cron it will remain silent unless the offset is breached at which point it will emit an email to the submitting owner of the job. -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
Re: [GENERAL] UFS2 Snapshots and Postgres
On 6/25/2012 7:35 AM, Eduardo Morras wrote:
Hi everyone,
I'm using FreeBSD 9 for Postgres and want to know if these actions are
safe for make a backup of the database:
a) call pg_start_backup('b1')
b) take an UFS2 snapshot of data files
c) call pg_stop_backup()
d) change to the snapshot dir and rsync/dd/dump/transfer it to backup
file server
Is it safe to call pg_start_backup('b1',true)?
Thanks in advance
Snapshots are safe (but will result in a roll-forward on restart) IF
AND ONLY IF the log data and database table spaces are all on the same
snapshotted volume.
IF THEY ARE NOT then it will probably work 95% of the time, and the
other 5% it will be unrecoverable. Be very, very careful -- the
snapshot must in fact snapshot ALL of the involved database volumes (log
data included!) at the same instant.
--
Karl Denninger
k...@denninger.net
/The Market Ticker/
Re: [GENERAL] Attempting to do a rolling move to 9.2Beta (as a slave) fails
On 5/28/2012 11:44 AM, Tom Lane wrote: Karl Denninger k...@denninger.net writes: I am attempting to validate the path forward to 9.2, and thus tried the following: 1. Build 9.2Beta1; all fine. 2. Run a pg_basebackup from the current master machine (running 9.1) to a new directory on the slave machine, using the 9.2Beta1 pg_basebackup executable. 3. Run a pg_upgrade against that from the new binary directory, producing a 9.2Beta1 data store. I do not think this can work, unless pg_basebackup is more magic than I think it is. AFAIK, what you have after step 2 is a non-self-consistent data directory that needs to be fixed by WAL replay before it is consistent. And pg_upgrade needs a consistent starting point. Actually when pg_upgrade starts it starts the old binary against the old data directory first, and thus replays the WAL records until it reaches consistency before it does the upgrade. It /*does*/ work; you have to specify that you want the WAL records during the pg_basebackup (e.g. -x=stream) so you have the WAL files for the old binary to consider during the startup (or manually ship them after the backup completes.) 4. Attempt to start the result as a SLAVE against the existing 9.1 master. This is definitely not going to work. You can only log-ship between servers of the same major version. OK. But the last step fails, claiming that wal_level was set to minimal when the WAL records were written. No it wasn't. Not only was it not on the master where the base backup came from, it wasn't during the upgrade either nor is it set that way on the new candidate slave. Is this caused by the version mismatch? Note that it does NOT bitch about the versions not matching. That sounds like a bug, or poorly sequenced error checks. regards, tom lane Well, at least I know why it fails and that it's a bad error message (and can't work) rather than something stupid in the original setup (which looked ok.) -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
[GENERAL] Attempting to do a rolling move to 9.2Beta (as a slave) fails
Here's what I'm trying to do in testing 9.2Beta1. The current configuration is a master and a hot standby at a diverse location for both hot swap and online backup. Both are archived regularly so if something goes south I can recover (to either as a master.) I am attempting to validate the path forward to 9.2, and thus tried the following: 1. Build 9.2Beta1; all fine. 2. Run a pg_basebackup from the current master machine (running 9.1) to a new directory on the slave machine, using the 9.2Beta1 pg_basebackup executable. 3. Run a pg_upgrade against that from the new binary directory, producing a 9.2Beta1 data store. 4. Attempt to start the result as a SLAVE against the existing 9.1 master. Everything is ok until I try to start the result as a slave. I would think I should be able to, since this is exactly the procedure (minus the upgrade) that I used to get the slave in operation in the first place (although I did the archive/dump/copy to the slave machine manually rather than use pg_basebackup to get it.) But the last step fails, claiming that wal_level was set to minimal when the WAL records were written. No it wasn't. Not only was it not on the master where the base backup came from, it wasn't during the upgrade either nor is it set that way on the new candidate slave. Is this caused by the version mismatch? Note that it does NOT bitch about the versions not matching. For obvious reasons I'm not interested in rolling the production master up to 9.2 until it's released, but running a second instance of my HA code against it as a slave would allow me to perform a very complete set of tests against 9.2Beta1 without any hassle or operational risks, yet keep the full working data set available and online during the testing. Do I need to run a complete parallel environment instead of trying to attach a 9.2Beta1 slave to an existing 9.1 master? (and if so, why doesn't the code complain about the mismatch instead of the bogus WAL message?) -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
Re: [GENERAL] Attempting to do a rolling move to 9.2Beta (as a slave) fails
On 5/27/2012 11:08 PM, Jan Nielsen wrote: Hi Karl, On Sun, May 27, 2012 at 9:18 PM, Karl Denninger k...@denninger.net mailto:k...@denninger.net wrote: Here's what I'm trying to do in testing 9.2Beta1. The current configuration is a master and a hot standby at a diverse location for both hot swap and online backup. Both are archived regularly so if something goes south I can recover (to either as a master.) Okay 1. Build 9.2Beta1; all fine. 2. Run a pg_basebackup from the current master machine (running 9.1) to a new directory on the slave machine, using the 9.2Beta1 pg_basebackup executable. 3. Run a pg_upgrade against that from the new binary directory, producing a 9.2Beta1 data store. 4. Attempt to start the result as a SLAVE against the existing 9.1 master. Hmm - that's likely a problem: In general, log shipping between servers running different major PostgreSQL release levels is not possible. [1] Is this caused by the version mismatch? Probably. Then the error message is wrong :-) Do I need to run a complete parallel environment instead of trying to attach a 9.2Beta1 slave to an existing 9.1 master? (and if so, why doesn't the code complain about the mismatch instead of the bogus WAL message?) Slony [2] or PGBouncer+Londiste [3] should allow you to do this in an integrated fashion. [4] I ran Slony for quite a while before 9.x showed up; I could put it back into use for a while but I really like the integrated setup that exists now with 9.x. I'll look at doing a parallel setup but it will more limited in what I can actually validate against in terms of workload than the above was workable... -- -- Karl Denninger /The Market Ticker ®/ http://market-ticker.org Cuda Systems LLC
Re: [GENERAL] Postgre 9.0 replication using streaming.
On 10/5/2010 2:12 PM, Chris Barnes wrote: I would like to know if there is a way to configure 9 to do this. I have 4 unique databases running on 4 servers. I would like to have them replicate to a remote site for disaster recovery. I would like to consolidate these 4 database into one machine and use streaming replication from the 4 masters at out local collocation site. Is there a way configure postgres 9.0 to have 4 unique streaming connections from this one machine with the 4 databases to the 4 databases on 4 machines. Thanks for your reply, Chris Barnes CBIT Inc. No, but you could set up four separate instances of Postgres and run all four on one machine. -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Recovery - Automated Monitoring
On 10/3/2010 1:34 AM, Guillaume Lelarge wrote: Le 03/10/2010 07:07, Karl Denninger a écrit : On 10/2/2010 11:40 PM, Rajesh Kumar Mallah wrote: I hope u checked point #11 http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use * *11.* You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using /pg_current_xlog_location/ on the primary and the /pg_last_xlog_receive_location///pg_last_xlog_replay_location/ on the standby, respectively. $ psql -c SELECT pg_current_xlog_location() -h192.168.0.10 (primary host) pg_current_xlog_location -- 0/200 (1 row) $ psql -c select pg_last_xlog_receive_location() -h192.168.0.20 (standby host) pg_last_xlog_receive_location --- 0/200 (1 row) $ psql -c select pg_last_xlog_replay_location() -h192.168.0.20 (standby host) pg_last_xlog_replay_location -- 0/200 (1 row) Regds Rajesh Kumar Mallah. Yes, I did. Now how do I get an arithmetic difference between the two? There will (usually) be a small difference between the master and slave on a busy system - what I want to do is query both and if the difference in their locations is greater than some defined size, start raising hell (e.g. sending SMS to people, etc) I can SEE the difference, but I don't see a way to COMPUTE a difference, and there does not appear to be a function that will accept the log file location as an argument for conversion - the one documented for offsets (which might otherwise work) does not work on the slave as I noted. With Slony there was a set of system tables that would tell me how many unapplied changes were in the queue. From this I could determine health - if the number was more than some reasonably-small amount, something was broken and alarms were to be raised. I'm looking for a way to implement the same sort of functionality here. ticker=# select pg_last_xlog_replay_location(); pg_last_xlog_replay_location -- 37A/327D1888 (1 row) ticker=# select pg_current_xlog_location(); pg_current_xlog_location -- 37A/3280DCB8 (1 row) How do I get an arithmetic difference between these two programmatically, and will such always be monoatomically increasing (that is, will they ever roll over, thereby giving me a potential NEGATIVE difference?) pgPool-II does that computation. You should check that in its source code. File pool_worker_child.c, function check_replication_time_lag(). It creates a long value from the LSN returned by these functions. Here is the computation: lsn = xlogid * 16 * 1024 * 1024 * 255 + xrecoff; In your example, xlogid is 37A and xrecoff is 327D1888 on the slave and 3280DCB8 on the master. The hexadecimal values were first converted to their unsigned decimal integer representation (same file, function text_to_lsn()). You should really get a look at this file. It's probably a better explanation than what I'm trying to do :) Here's a little program to do it - change MAX_OFFSET and modify (particularly the notify function) to suit and place a file called CHECK.cluster in the following form in the home directory of whatever this runs as (it will look in the home directory of the euid of the process): master-name connection-string-to-connect-to-master slave1-name slave-string-to-connect slave2-name slave-string-to-connect .. It will emit a notification for each slave that is more than MAX_OFFSET behind, or if any slave is not in recovery mode (bad; it thinks it's a master!) or worse, if the MASTER is in recovery mode, or if it can't talk to any of the declared nodes. Note that CHECK.cluster contains connection information so make sure you run this as someone and with a home directory for that someone appropriately permitted to prevent leakage of the connection credentials. Ain't gonna claim it's elegant, but it was something I was able to quickly bang out in a few minutes and it works. Stuck in the CRON if you have the cron's email going somewhere that will get noticed fast (e.g. a Blackberry email, etc) you can run this on whatever interval you need and it'll do the job. Postgresql's development team can consider this a contribution to the codebase if someone wants to clean it up a bit and include it. You could obviously pick up the config file as an argument rather than using the running euid of the process to grab it; I'm using it in an environment where the latter works better for me - YMMV. /* * check-replication - Checks the replication state of a Postgresql master/slave cluster, with multiple slaves and * one master. Each slave's streaming log location is checked against the master in turn, and if any are out of * sync by more than the allowed amount
Re: [GENERAL] Streaming Recovery - Automated Monitoring - CODE LINK
On 10/3/2010 3:44 PM, Karl Denninger wrote: On 10/3/2010 1:34 AM, Guillaume Lelarge wrote: Le 03/10/2010 07:07, Karl Denninger a écrit : On 10/2/2010 11:40 PM, Rajesh Kumar Mallah wrote: I hope u checked point #11 http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use * *11.* You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using /pg_current_xlog_location/ on the primary and the /pg_last_xlog_receive_location///pg_last_xlog_replay_location/ on the standby, respectively. $ psql -c SELECT pg_current_xlog_location() -h192.168.0.10 (primary host) pg_current_xlog_location -- 0/200 (1 row) $ psql -c select pg_last_xlog_receive_location() -h192.168.0.20 (standby host) pg_last_xlog_receive_location --- 0/200 (1 row) $ psql -c select pg_last_xlog_replay_location() -h192.168.0.20 (standby host) pg_last_xlog_replay_location -- 0/200 (1 row) Regds Rajesh Kumar Mallah. Yes, I did. Now how do I get an arithmetic difference between the two? There will (usually) be a small difference between the master and slave on a busy system - what I want to do is query both and if the difference in their locations is greater than some defined size, start raising hell (e.g. sending SMS to people, etc) I can SEE the difference, but I don't see a way to COMPUTE a difference, and there does not appear to be a function that will accept the log file location as an argument for conversion - the one documented for offsets (which might otherwise work) does not work on the slave as I noted. With Slony there was a set of system tables that would tell me how many unapplied changes were in the queue. From this I could determine health - if the number was more than some reasonably-small amount, something was broken and alarms were to be raised. I'm looking for a way to implement the same sort of functionality here. ticker=# select pg_last_xlog_replay_location(); pg_last_xlog_replay_location -- 37A/327D1888 (1 row) ticker=# select pg_current_xlog_location(); pg_current_xlog_location -- 37A/3280DCB8 (1 row) How do I get an arithmetic difference between these two programmatically, and will such always be monoatomically increasing (that is, will they ever roll over, thereby giving me a potential NEGATIVE difference?) pgPool-II does that computation. You should check that in its source code. File pool_worker_child.c, function check_replication_time_lag(). It creates a long value from the LSN returned by these functions. Here is the computation: lsn = xlogid * 16 * 1024 * 1024 * 255 + xrecoff; In your example, xlogid is 37A and xrecoff is 327D1888 on the slave and 3280DCB8 on the master. The hexadecimal values were first converted to their unsigned decimal integer representation (same file, function text_to_lsn()). You should really get a look at this file. It's probably a better explanation than what I'm trying to do :) Here's a little program to do it - change MAX_OFFSET and modify (particularly the notify function) to suit and place a file called CHECK.cluster in the following form in the home directory of whatever this runs as (it will look in the home directory of the euid of the process): master-name connection-string-to-connect-to-master slave1-name slave-string-to-connect slave2-name slave-string-to-connect .. It will emit a notification for each slave that is more than MAX_OFFSET behind, or if any slave is not in recovery mode (bad; it thinks it's a master!) or worse, if the MASTER is in recovery mode, or if it can't talk to any of the declared nodes. Note that CHECK.cluster contains connection information so make sure you run this as someone and with a home directory for that someone appropriately permitted to prevent leakage of the connection credentials. Ain't gonna claim it's elegant, but it was something I was able to quickly bang out in a few minutes and it works. Stuck in the CRON if you have the cron's email going somewhere that will get noticed fast (e.g. a Blackberry email, etc) you can run this on whatever interval you need and it'll do the job. Postgresql's development team can consider this a contribution to the codebase if someone wants to clean it up a bit and include it. You could obviously pick up the config file as an argument rather than using the running euid of the process to grab it; I'm using it in an environment where the latter works better for me - YMMV. I've cleaned this up a bit more and it is now at http://www.denninger.net/check-replication.c Feel free to grab and use it if you think it would be helpful. -- Karl attachment: karl.vcf -- Sent via pgsql
[GENERAL] Streaming Recovery - Automated Monitoring
I'm trying to come up with an automated monitoring system to watch the WAL log progress and sound appropriate alarms if it gets too far behind for some reason (e.g. communications problems, etc.) - so far without success. What I need is some sort of way to compute a difference between the master and slave(s) position in the WAL stream. It appears that there is no function set specifically to do this, and one of the things I thought I'd try (using pg_xlogfile_name_offset) doesn't work on the slave - it returns: ticker=# select pg_xlogfile_name_offset(pg_last_xlog_receive_location()); ERROR: recovery is in progress HINT: pg_xlogfile_name_offset() cannot be executed during recovery. Any ideas? Looking in on the system is ok, but I want to come up with an automated tool for letting me know if something goes haywire. Thanks in advance. -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Recovery - Automated Monitoring
On 10/2/2010 11:40 PM, Rajesh Kumar Mallah wrote: I hope u checked point #11 http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use * *11.* You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using /pg_current_xlog_location/ on the primary and the /pg_last_xlog_receive_location///pg_last_xlog_replay_location/ on the standby, respectively. $ psql -c SELECT pg_current_xlog_location() -h192.168.0.10 (primary host) pg_current_xlog_location -- 0/200 (1 row) $ psql -c select pg_last_xlog_receive_location() -h192.168.0.20 (standby host) pg_last_xlog_receive_location --- 0/200 (1 row) $ psql -c select pg_last_xlog_replay_location() -h192.168.0.20 (standby host) pg_last_xlog_replay_location -- 0/200 (1 row) Regds Rajesh Kumar Mallah. Yes, I did. Now how do I get an arithmetic difference between the two? There will (usually) be a small difference between the master and slave on a busy system - what I want to do is query both and if the difference in their locations is greater than some defined size, start raising hell (e.g. sending SMS to people, etc) I can SEE the difference, but I don't see a way to COMPUTE a difference, and there does not appear to be a function that will accept the log file location as an argument for conversion - the one documented for offsets (which might otherwise work) does not work on the slave as I noted. With Slony there was a set of system tables that would tell me how many unapplied changes were in the queue. From this I could determine health - if the number was more than some reasonably-small amount, something was broken and alarms were to be raised. I'm looking for a way to implement the same sort of functionality here. ticker=# select pg_last_xlog_replay_location(); pg_last_xlog_replay_location -- 37A/327D1888 (1 row) ticker=# select pg_current_xlog_location(); pg_current_xlog_location -- 37A/3280DCB8 (1 row) How do I get an arithmetic difference between these two programmatically, and will such always be monoatomically increasing (that is, will they ever roll over, thereby giving me a potential NEGATIVE difference?) The offset function doesn't work on the slave, but that probably doesn't help me anyway since it appears to be file-relative (that is, if the prefix is different its useless anyway.) If there is no internal Postgres functionality that can do this, then I need to know the computational rules for how to get an absolute offset between two different values returned by these functions. -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Somewhat odd messages being logged on replicated server
I am playing with the replication on 9.0 and running into the following. I have a primary that is running at a colo, and is replicated down to a secondary here using SLONY. This is working normally. I decided to set up a replication of the SLONY secondary onto my sandbox machine to see what I think of it as a solution to potentially replace SLONY. All appears to be working fine, EXCEPT that I'm getting the following in the log. Sep 29 19:58:54 dbms2 postgres[8564]: [2-2] STATEMENT: update post set views = (select views from post where number='116763' and toppost='1') + 1 where number='116763' and toppost='1' Sep 29 20:01:11 dbms2 postgres[8581]: [2-1] ERROR: cannot execute UPDATE in a read-only transaction Sep 29 20:01:11 dbms2 postgres[8581]: [2-2] STATEMENT: update post set views = (select views from post where number='2040327' and toppost='1') + 1 where number='2040327' and toppost='1' Sep 29 20:02:02 dbms2 postgres[8582]: [2-1] ERROR: cannot execute UPDATE in a read-only transaction Sep 29 20:02:02 dbms2 postgres[8582]: [2-2] STATEMENT: update post set views = (select views from post where number='140406' and toppost='1') + 1 where number='140406' and toppost='1' Sep 29 20:04:58 dbms2 postgres[8586]: [2-1] ERROR: cannot execute UPDATE in a read-only transaction When I go look at the value of views on both the master and replicated slave, they have the same value. so I'm not sure why the error is showing up. There are no clients attempting to connect to the replicated server at all at this point (I will enable that later once I'm satisfied that it is working in the general sense), so this has to be coming from the replication system itself. I presume that since the values are identical on both machines this can be safely ignored, but I'm curious why it's happening -- Karl attachment: karl.vcf -- 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] Somewhat odd messages being logged on replicated server
On 9/29/2010 8:55 PM, Jeff Davis wrote: On Wed, 2010-09-29 at 20:04 -0500, Karl Denninger wrote: Sep 29 19:58:54 dbms2 postgres[8564]: [2-2] STATEMENT: update post set views = (select views from post where number='116763' and toppost='1') + 1 where number='116763' and toppost='1' Sep 29 20:01:11 dbms2 postgres[8581]: [2-1] ERROR: cannot execute UPDATE in a read-only transaction Sep 29 20:01:11 dbms2 postgres[8581]: [2-2] STATEMENT: update post set views = (select views from post where number='2040327' and toppost='1') + 1 where number='2040327' and toppost='1' Sep 29 20:02:02 dbms2 postgres[8582]: [2-1] ERROR: cannot execute UPDATE in a read-only transaction Sep 29 20:02:02 dbms2 postgres[8582]: [2-2] STATEMENT: update post set views = (select views from post where number='140406' and toppost='1') + 1 where number='140406' and toppost='1' Sep 29 20:04:58 dbms2 postgres[8586]: [2-1] ERROR: cannot execute UPDATE in a read-only transaction When I go look at the value of views on both the master and replicated slave, they have the same value. so I'm not sure why the error is showing up. There are no clients attempting to connect to the replicated server at all at this point (I will enable that later once I'm satisfied that it is working in the general sense), so this has to be coming from the replication system itself. It looks very much like clients are connecting and issuing UPDATEs (or trying to). Can you re-examine the situation? Perhaps try changing pg_hba.conf to be sure nobody is connecting. 9.0-style replication doesn't issue queries by itself. I'm VERY sure nobody is connecting - the machine in question is behind a firewall! In addition here's the netstat on it: $ netstat -a -n|more Active Internet connections (including servers) Proto Recv-Q Send-Q Local Address Foreign Address (state) tcp4 0 0 192.168.1.202.22 192.168.1.40.51232 ESTABLISHED tcp4 0 0 192.168.1.202.61119192.168.1.201.5432 ESTABLISHED tcp4 0 0 *.5432 *.*LISTEN tcp6 0 0 *.5432 *.*LISTEN tcp4 0 0 *.514 *.*LISTEN tcp4 0 0 127.0.0.1.25 *.*LISTEN tcp4 0 0 *.22 *.*LISTEN tcp6 0 0 *.22 *.*LISTEN udp6 0 0 ::1.22165 ::1.22165 udp4 0 0 127.0.0.1.123 *.* udp6 0 0 ::1.123*.* udp6 0 0 fe80:3::1.123 *.* udp4 0 0 192.168.1.202.123 *.* udp6 0 0 *.123 *.* udp4 0 0 *.123 *.* udp4 0 0 *.514 *.* udp6 0 0 *.514 *.* The only connection to a Postgresql server is to the master I am pulling the replication from (201) No other connections - the listener is there, but nobody's connected to it. Also, the UPDATEs look unsafe by themselves (replication or not). If two such updates are executed simultaneously, views might not be updated twice. Instead, try: update post set views = views + 1 where number='140406' and toppost='1' Regards, Jeff Davis That's actually ok - the update itself is a legitimate statement on the master, posted to that table on a reply, and is part of a transaction. I thought logshipping like this over TCP would present the actual WAL changes (e.g. page changes) and was quite surprised when I saw what look very much like actual statements in the logfile. A process status also shows no postgres processes. All of the real application connections are on a machine 1200 miles away and behind pgpool, so if there WAS a connection it would be persistent. It's not there (never mind that the clients don't have an entry in pg_hba that would work, nor could they cross the firewall since there is no port forwarding in the firewall to allow it.) I'll investigate this further. -- Karl -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] One warning on migration from 8.4 -- 9.0
If you use Slony, expect it to lose the replication status. I attempted the following: 1. Master and slaves on 8.4. 2. Upgrade one slave to 9.0. Shut it down, used pg_upgrade to perform the upgrade. 3. Restarted the slave. Slony appeared to come up, but said it was syncing only TWO tables (out of the 33 in the working set!) Of course that didn't work very well at all. Dropping the replication set and re-adding it appears to be fine, but of course that caused a full database copy. Not too cool. I have no idea why it only thought there were two tables in the replication set. Very, very odd stuff Something to be aware of - I haven't figured out why it did this, nor do I know if it will do the same thing to me when I attempt to upgrade the master to 9.0 - that's something I won't attempt until the weekend at the earliest. Other than that running with 8.4 for the master and 9.0 for the slaves appears to be ok. -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with pg_convert from 8.4 - 9.0
Uh, is there a way around this problem? $ bin/pg_upgrade -c -d /usr/local/pgsql-8.4/data -D data -b /usr/local/pgsql-8.4/bin -B bin Performing Consistency Checks - Checking old data directory (/usr/local/pgsql-8.4/data) ok Checking old bin directory (/usr/local/pgsql-8.4/bin) ok Checking new data directory (data) ok Checking new bin directory (bin)ok Checking for reg* system oid user data typesfatal | Your installation contains one of the reg* data types in | user tables. These data types reference system oids that | are not preserved by pg_upgrade, so this cluster cannot | currently be upgraded. You can remove the problem tables | and restart the migration. A list of the problem columns | is in the file: | /usr/local/pgsql/tables_using_reg.txt $ more tables_using_reg.txt Database: marketticker public.pg_ts_dict.dict_init public.pg_ts_dict.dict_lexize public.pg_ts_parser.prs_start public.pg_ts_parser.prs_nexttoken public.pg_ts_parser.prs_end public.pg_ts_parser.prs_headline public.pg_ts_parser.prs_lextype Database: ticker public.pg_ts_dict.dict_init public.pg_ts_dict.dict_lexize public.pg_ts_parser.prs_start public.pg_ts_parser.prs_nexttoken public.pg_ts_parser.prs_end public.pg_ts_parser.prs_headline public.pg_ts_parser.prs_lextype $ These two databases use the full-text Tsearch2 module. I can't remove these tables either; they're marked system and even as a superuser removing them is not permitted. Does this mean that I have to dump and restore, and can't use convert? If so that's a real bummer. -- Karl attachment: karl.vcf -- 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] Problem with pg_convert from 8.4 - 9.0
On 9/21/2010 10:16 PM, Bruce Momjian wrote: Karl Denninger wrote: Uh, is there a way around this problem? $ bin/pg_upgrade -c -d /usr/local/pgsql-8.4/data -D data -b /usr/local/pgsql-8.4/bin -B bin Performing Consistency Checks - Checking old data directory (/usr/local/pgsql-8.4/data) ok Checking old bin directory (/usr/local/pgsql-8.4/bin) ok Checking new data directory (data) ok Checking new bin directory (bin)ok Checking for reg* system oid user data typesfatal | Your installation contains one of the reg* data types in | user tables. These data types reference system oids that | are not preserved by pg_upgrade, so this cluster cannot | currently be upgraded. You can remove the problem tables | and restart the migration. A list of the problem columns | is in the file: | /usr/local/pgsql/tables_using_reg.txt $ more tables_using_reg.txt Database: marketticker public.pg_ts_dict.dict_init public.pg_ts_dict.dict_lexize public.pg_ts_parser.prs_start public.pg_ts_parser.prs_nexttoken public.pg_ts_parser.prs_end public.pg_ts_parser.prs_headline public.pg_ts_parser.prs_lextype Database: ticker public.pg_ts_dict.dict_init public.pg_ts_dict.dict_lexize public.pg_ts_parser.prs_start public.pg_ts_parser.prs_nexttoken public.pg_ts_parser.prs_end public.pg_ts_parser.prs_headline public.pg_ts_parser.prs_lextype $ These two databases use the full-text Tsearch2 module. I can't remove these tables either; they're marked system and even as a superuser removing them is not permitted. I belive you are incorrect. They are in the public schema so I am not sure how they are system. They are prefixed with pg_ but that should not be a problem. Does this mean that I have to dump and restore, and can't use convert? If so that's a real bummer. Usually I tell folks to dump/delete/reload those tables and use pg_upgrade for everything else, as the error message suggests. That appears to be working not sure why it didn't before Thanks. -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Encoding change question...
So I have myself a nice pickle here. I've got a database which was originally created with SQL_ASCII for the encoding (anything goes text fields) Unfortunately, I have a bunch of data that was encoded in UTF-8 that's in an RSS feed that I need to load into said database. iconv barfs all over this file in an attempt to turn it into ISO-8859 (which is what the web application currently attached to that database is emitting and collecting.) It appears the problem is (mostly) things like the stylized double-quotes. So I figured I'd go the other way, and convert what I have now in the tables into UTF8. Well, except that doesn't work either. ticker=# select convert_to(subject, 'utf8') from post where ordinal='2098167'; convert_to 1%: the interest rate on IBM\222s most recent three-year bond. (1 row) \222 is the correct code point for the styled single apostrophe that is in that place in ISO-8859-1 in the source. However, the UTF prefix is missing, as are the other two code-point characters (that is, I got the code point but not the other two bytes that should be in front of it. And if I set the code page on the web site to UTF-8, and also set the encoding on the SQL session to UTF-8 I don't get the three-byte code - I just get the one byte. That's a bust. There are TWO fields in this database that need converted. I presumed I could do something like this: # update post set field1 = convert_to(field1, 'utf8'); It runs to completion without complaint but produces the above. No good. So is there a way to do this? I do NOT want to dump, iconv on the dumped file, then reload the database if I can possibly avoid it. Yes, I know that will work (I've tested it on my development box), but being able to do this hot would be DRAMATICALLY preferred. If I do the iconv route I am basically rebuilding the entire database with all that's involved in doing so in terms of downtime and such. VERY undesirable. (Equally-valid would be a way to convert UTF-8 to ISO-8859-1 (Latin1), thereby allowing me to convert the incoming data stream to what's already in the system, but thus far I've found no joy on that at all.) Ideas? -- Karl attachment: karl.vcf -- 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] Encoding change question...
Peter C. Lai wrote: The doublequotes isn't UTF8 it's people copying and pasting from Microsoft stuff, which is WIN-1252. So try to use that with iconv instead of utf8 On 2010-08-16 12:40:03PM -0500, Karl Denninger wrote: So I have myself a nice pickle here. I've got a database which was originally created with SQL_ASCII for the encoding (anything goes text fields) Unfortunately, I have a bunch of data that was encoded in UTF-8 that's in an RSS feed that I need to load into said database. iconv barfs all over this file in an attempt to turn it into ISO-8859 (which is what the web application currently attached to that database is emitting and collecting.) It appears the problem is (mostly) things like the stylized double-quotes. So I figured I'd go the other way, and convert what I have now in the tables into UTF8. Well, except that doesn't work either. ticker=# select convert_to(subject, 'utf8') from post where ordinal='2098167'; convert_to 1%: the interest rate on IBM\222s most recent three-year bond. (1 row) \222 is the correct code point for the styled single apostrophe that is in that place in ISO-8859-1 in the source. However, the UTF prefix is missing, as are the other two code-point characters (that is, I got the code point but not the other two bytes that should be in front of it. And if I set the code page on the web site to UTF-8, and also set the encoding on the SQL session to UTF-8 I don't get the three-byte code - I just get the one byte. That's a bust. There are TWO fields in this database that need converted. I presumed I could do something like this: # update post set field1 = convert_to(field1, 'utf8'); It runs to completion without complaint but produces the above. No good. So is there a way to do this? I do NOT want to dump, iconv on the dumped file, then reload the database if I can possibly avoid it. Yes, I know that will work (I've tested it on my development box), but being able to do this hot would be DRAMATICALLY preferred. If I do the iconv route I am basically rebuilding the entire database with all that's involved in doing so in terms of downtime and such. VERY undesirable. (Equally-valid would be a way to convert UTF-8 to ISO-8859-1 (Latin1), thereby allowing me to convert the incoming data stream to what's already in the system, but thus far I've found no joy on that at all.) Ideas? -- Karl No, the problem is that the existing data in the database and the web app that are using it are both ISO-8859-1. The issue is that the data I need to load INTO the system is in UTF-8 (and really is), and is full of three-byte escapes. iconv barfs all over it trying to go to ISO-8859-1, so I can't convert the INCOMING data to what's in the system now. It also won't convert it to Windows-1252 (which is kind of a superset of ISO-8859, and thus the server might not toss on it too badly.) FS/karl:~/tmp iconv -f UTF-8 -t WINDOWS-1252 rss-marketticker.php /dev/null iconv: (stdin):2766:6125: cannot convert FS/karl:~/tmp iconv -f UTF-8 -t ISO-8859-1 rss-marketticker.php /dev/null iconv: (stdin):222:391: cannot convert The data set that is incoming is some ~30,000 records. The data in the system already is ~2m records. Obviously, if I can convert the incoming data that's the better choice, but it appears I can't. Thus the desire to go the other way - turn the existing data in the database into UTF-8, which is probably desirable in the future anyway. I can iconv a pg_dumpall of the database TO UTF-8 and it succeeds (at least in theory), but the database into which I want to load this data set is HUGE and dumping and reloading it isn't on my A list of things to do. If I have to I have to - but if I can avoid this I certainly WANT TO. What I don't understand is why the Postgres function convert_to() doesn't emit the THREE BYTE sequence, and only emits the codepoint. -- Karl attachment: karl.vcf -- 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] Compression on SSL links?
Bruce Momjian wrote: Craig Ringer wrote: On 13/08/2010 9:31 PM, Bruce Momjian wrote: Karl Denninger wrote: I may be blind - I don't see a way to enable this. OpenSSL kinda supports this - does Postgres' SSL connectivity allow it to be supported/enabled? What are you asking, exactly? As far as I can tell they're asking for transport-level compression, using gzip or similar, in much the same way as SSL/TLS currently provides transport-level encryption. Compression at the postgresql protocol level or above, so it's invisible at the level of the libpq APIs for executing statements and processing results, and doesn't change SQL processing. Since remote access is often combined with SSL, which is already supported by libpq, using SSL-integrated compression seems pretty promising if it's viable in practice. It'd avoid the pain of having to add compression to the Pg protocol by putting it outside the current protocol, in the SSL layer. Even better, compressing results before encrypting them makes the encrypted traffic *much* stronger against known-plaintext and pattern-based attacks. And, of course, compressing the content costs CPU time but reduces the amount of data that must then be compressed. OpenSSL does provide some transparent crypto support. See: http://www.openssl.org/docs/ssl/SSL_COMP_add_compression_method.html I thought all SSL traffic was compressed, unless you turned that off. It is just SSH that is always compressed? SSL is NOT always compressed at the data level. SSH is if you ask for it, but by default SSL is NOT. This is a common (and false) belief. -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compression on SSL links?
I may be blind - I don't see a way to enable this. OpenSSL kinda supports this - does Postgres' SSL connectivity allow it to be supported/enabled? - Karl attachment: karl.vcf -- 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] Query memory usage
Tom Duffey wrote: Hi Everyone, I have a table with several hundred million rows of timestamped values. Using pg_dump we are able to dump the entire table to disk no problem. However, I would like to retrieve a large subset of data from this table using something like: COPY (SELECT * FROM history WHERE timestamp '2009-01-01') TO STDOUT; Executing this query causes our server to consume all available swap and crash. Can anyone help me figure out what needs to be done to allow this query to execute? How long it takes doesn't really matter as long as it can be performed reliably. The database currently lives on a Red Hat EL 5.3 server with 16GB RAM and 4GB swap running PostgreSQL 8.3.7. Possibly relevant lines from postgresql.conf: shared_buffers = 4GB work_mem = 32MB maintenance_work_mem = 1GB effective_cache_size = 8GB Tom Is there an index on the time stamp? If not, create one - it will make possible the select without having to read the entire table (only the index) -- Karl attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query Issue with full-text search
This may better-belong in pgsql-sql but since it deals with a function
as opposed to raw SQL syntax I am sticking it here
Consider the following DBMS schema slice
Table public.post
Column | Type |
Modifiers
---+--+
subject | text |
message | text |
ordinal | integer | not null default
nextval('post_ordinal_seq'::regclass)
Indexes:
post_pkey PRIMARY KEY, btree (ordinal)
idx_message gin (to_tsvector('english'::text, message))
idx_subject gin (to_tsvector('english'::text, subject))
(there are a bunch more indices and columns in the table, but these are
the ones in question)
Now let's run a couple of queries on this:
ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('violence') order by modified desc limit
100;
QUERY
PLAN
Limit (cost=0.00..2456.32 rows=100 width=433) (actual
time=266.703..3046.310 rows=100 loops=1)
- Index Scan Backward using post_modified on post
(cost=0.00..240400.00 rows=9787 width=433) (actual
time=266.698..3045.920 rows=100 loops=1)
Filter: (to_tsvector('english'::text, message) @@
to_tsquery('violence'::text))
Total runtime: 3046.565 ms
(4 rows)
Ok, not too bad, considering that the table contains close to 2 million
rows - ~3 seconds is pretty good.
Now let's try something that's NOT in the database:
ticker=# explain analyze select * from post where to_tsvector('english',
message) @@ to_tsquery('hoseface') order by modified desc limit
100;
NOTICE: word is too long to be indexed
DETAIL: Words longer than 2047 characters are ignored.
QUERY
PLAN
---
Limit (cost=0.00..2456.32 rows=100 width=433) (actual
time=303350.036..303350.036 rows=0 loops=1)
- Index Scan Backward using post_modified on post
(cost=0.00..240400.00 rows=9787 width=433) (actual
time=303350.031..303350.031 rows=0 loops=1)
Filter: (to_tsvector('english'::text, message) @@
to_tsquery('hoseface'::text))
Total runtime: 303350.079 ms
(4 rows)
This is **UNBELIEVABLY** slow; indeed, it appears to have done a
sequential scan of the entire table!
Why?
One would think that if the index lookup fails it fails - and would fail
FAST, returning no rows. It appears that this is not the case, and the
system actually goes in and tries to look up the query off the message
contents, IGNORING the index!
That's not good for what should be obvious reasons. is the gin
index type screwed up in some form or fashion?
This behavior is relatively new. I'm running 8.4.3 and this started
happening some time before that - I believe it was an issue in 8.4.2,
but I KNOW it was not a problem when I was running 8.3. The confounding
factor is that the table has grown rapidly and as such not happening
before might be more due to the table size than the software release -
of that I cannot be certain.
The other possibility is that the NOTICE results in some sort of flag
being set that tells the query processor to ignore the index and perform
a sequential scan IF there's a failure to match. If this is the case I
will then have to write something to go through and find the offending
item and remove it.
-- Karl
attachment: karl.vcf
--
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] Problem with pg_compresslog'd archives
Has there been an update on this situation? Koichi Suzuki wrote: I understand the situation. I'll upload the improved code ASAP. -- Koichi Suzuki 2010/2/11 Karl Denninger k...@denninger.net: Will this come through as a commit on the pgfoundry codebase? I've subscribed looking for it The last edit, if I read the release notes and tracebacks on the codebase correctly, goes back to the early part of 2009 - which strongly implies that there are a **LOT** of people out there that could be running this code with un-restoreable archives! That, for obvious reasons, could be VERY, VERY bad if someone was to suffer a system crash Koichi Suzuki wrote: I found it's pg_compresslog problem (calculation of XNOOP record length used in pg_decompresslog).I'm fixing the bug and will upload the fix shortly. Sorry for inconvenience. -- Koichi Suzuki 2010/2/8 Karl Denninger k...@denninger.net: This may belong in a bug report, but I'll post it here first... There appears to be a **SERIOUS** problem with using pg_compresslog and pg_uncompresslog with Postgresql 8.4.2. Here's my configuration snippet: full_page_writes = on # recover from partial page writes wal_buffers = 256kB # min 32kB # (change requires restart) #wal_writer_delay = 200ms # 1-1 milliseconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 # - Checkpoints - checkpoint_segments = 64# in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables archive_command = 'test ! -f /dbms/pg_archive/%f.bz2 pg_compresslog %p | bzip2 - /dbms/pg_archive/%f.bz2' #command to use to archive a logfile segment All appears to be fine with the writes, and they are being saved off on the nightly backups without incident. I take a full dump using the instructions in the documentation and make sure I copy the proper must have file for consistency to be reached. The problem comes when I try to restore. recovery_conf contains: restore_command = '/usr/local/pgsql/recovery.sh %f %p' And that file contains: #! /bin/sh infile=$1 outfile=$2 if test -f /dbms/pg_archive/$infile.bz2 then bunzip2 -c /dbms/pg_archive/$infile.bz2 | /usr/local/pgsql/bin/pg_decompresslog - $outfile exit 0 else exit 1 fi == The problem is that it appears that some of the segments being saved are no good! On occasion I get this when trying to restore... Feb 7 12:43:51 dbms2 postgres[2001]: [210-1] LOG: restored log file 00010171009A from archive Feb 7 12:43:52 dbms2 postgres[2001]: [211-1] LOG: restored log file 00010171009B from archive Feb 7 12:43:52 dbms2 postgres[2001]: [212-1] LOG: restored log file 00010171009C from archive Feb 7 12:43:52 dbms2 postgres[2001]: [213-1] LOG: restored log file 00010171009D from archive Feb 7 12:43:53 dbms2 postgres[2001]: [214-1] LOG: restored log file 00010171009E from archive Feb 7 12:43:53 dbms2 postgres[2001]: [215-1] LOG: restored log file 00010171009F from archive Feb 7 12:43:54 dbms2 postgres[2001]: [216-1] LOG: restored log file 0001017100A0 from archive Feb 7 12:43:54 dbms2 postgres[2001]: [217-1] LOG: restored log file 0001017100A1 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [218-1] LOG: restored log file 0001017100A2 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [219-1] LOG: restored log file 0001017100A3 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [220-1] LOG: restored log file 0001017100A4 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [221-1] LOG: restored log file 0001017100A5 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [222-1] LOG: restored log file 0001017100A6 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [223-1] PANIC: corrupted page pointers: lower = 772, upper = 616, special = 0 Feb 7 12:43:57 dbms2 postgres[2001]: [223-2] CONTEXT: xlog redo hot_update: rel 1663/616245/1193269; tid 53/93; new 53/4 Feb 7 12:43:57 dbms2 postgres[2000]: [1-1] LOG: startup process (PID 2001) was terminated by signal 6: Abort trap Feb 7 12:43:57 dbms2 postgres[2000]: [2-1] LOG: terminating any other active server processes Eek. I assume this means that either A6 or A7 is corrupt. But I have the file both in the restore AND ON THE MACHINE WHERE IT ORIGINATED: On the SOURCE machine (which is running just fine): tickerforum# cksum *171*A[67]* 172998591 830621 0001017100A6.bz2 1283345296
Re: [GENERAL] Problem with pg_compresslog'd archives
Not in a huge hurry (fixed right is better than fixed fast); just trying to figure out where the fix is in terms of progress. Koichi Suzuki wrote: Not yet. I'm rebuilding the test suit for better testing. If you're in a hurry, I can send you an update for test. -- Koichi Suzuki 2010/4/19 Karl Denninger k...@denninger.net: Has there been an update on this situation? Koichi Suzuki wrote: I understand the situation. I'll upload the improved code ASAP. -- Koichi Suzuki 2010/2/11 Karl Denninger k...@denninger.net: Will this come through as a commit on the pgfoundry codebase? I've subscribed looking for it The last edit, if I read the release notes and tracebacks on the codebase correctly, goes back to the early part of 2009 - which strongly implies that there are a **LOT** of people out there that could be running this code with un-restoreable archives! That, for obvious reasons, could be VERY, VERY bad if someone was to suffer a system crash Koichi Suzuki wrote: I found it's pg_compresslog problem (calculation of XNOOP record length used in pg_decompresslog).I'm fixing the bug and will upload the fix shortly. Sorry for inconvenience. -- Koichi Suzuki 2010/2/8 Karl Denninger k...@denninger.net: This may belong in a bug report, but I'll post it here first... There appears to be a **SERIOUS** problem with using pg_compresslog and pg_uncompresslog with Postgresql 8.4.2. Here's my configuration snippet: full_page_writes =n # recover from partial page writes wal_buffers =56kB # min 32kB # (change requires restart) #wal_writer_delay =00ms # 1-1 milliseconds #commit_delay = # range 0-10, in microseconds #commit_siblings =# range 1-1000 # - Checkpoints - checkpoint_segments =4# in logfile segments, min 1, 16MB each #checkpoint_timeout =min # range 30s-1h checkpoint_completion_target =.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning =0s # 0 disables archive_command =test ! -f /dbms/pg_archive/%f.bz2 pg_compresslog %p | bzip2 - /dbms/pg_archive/%f.bz2' #command to use to archive a logfile segment All appears to be fine with the writes, and they are being saved off on the nightly backups without incident. I take a full dump using the instructions in the documentation and make sure I copy the proper must have file for consistency to be reached. The problem comes when I try to restore. recovery_conf contains: restore_command =/usr/local/pgsql/recovery.sh %f %p' And that file contains: #! /bin/sh infile= outfile= if test -f /dbms/pg_archive/$infile.bz2 then bunzip2 -c /dbms/pg_archive/$infile.bz2 | /usr/local/pgsql/bin/pg_decompresslog - $outfile exit 0 else exit 1 fi The problem is that it appears that some of the segments being saved are no good! On occasion I get this when trying to restore... Feb 7 12:43:51 dbms2 postgres[2001]: [210-1] LOG: restored log file 00010171009A from archive Feb 7 12:43:52 dbms2 postgres[2001]: [211-1] LOG: restored log file 00010171009B from archive Feb 7 12:43:52 dbms2 postgres[2001]: [212-1] LOG: restored log file 00010171009C from archive Feb 7 12:43:52 dbms2 postgres[2001]: [213-1] LOG: restored log file 00010171009D from archive Feb 7 12:43:53 dbms2 postgres[2001]: [214-1] LOG: restored log file 00010171009E from archive Feb 7 12:43:53 dbms2 postgres[2001]: [215-1] LOG: restored log file 00010171009F from archive Feb 7 12:43:54 dbms2 postgres[2001]: [216-1] LOG: restored log file 0001017100A0 from archive Feb 7 12:43:54 dbms2 postgres[2001]: [217-1] LOG: restored log file 0001017100A1 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [218-1] LOG: restored log file 0001017100A2 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [219-1] LOG: restored log file 0001017100A3 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [220-1] LOG: restored log file 0001017100A4 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [221-1] LOG: restored log file 0001017100A5 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [222-1] LOG: restored log file 0001017100A6 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [223-1] PANIC: corrupted page pointers: lower =72, upper = 616, special = 0 Feb 7 12:43:57 dbms2 postgres[2001]: [223-2] CONTEXT: xlog redo hot_update: rel 1663/616245/1193269; tid 53/93; new 53/4 Feb 7 12:43:57 dbms2 postgres[2000]: [1-1] LOG: startup process (PID 2001) was terminated by signal 6: Abort trap Feb 7 12:43:57 dbms2 postgres[2000]: [2-1] LOG: terminating any
Re: [GENERAL] Problem with pg_compresslog'd archives
Will this come through as a commit on the pgfoundry codebase? I've subscribed looking for it The last edit, if I read the release notes and tracebacks on the codebase correctly, goes back to the early part of 2009 - which strongly implies that there are a **LOT** of people out there that could be running this code with un-restoreable archives! That, for obvious reasons, could be VERY, VERY bad if someone was to suffer a system crash Koichi Suzuki wrote: I found it's pg_compresslog problem (calculation of XNOOP record length used in pg_decompresslog).I'm fixing the bug and will upload the fix shortly. Sorry for inconvenience. -- Koichi Suzuki 2010/2/8 Karl Denninger k...@denninger.net: This may belong in a bug report, but I'll post it here first... There appears to be a **SERIOUS** problem with using pg_compresslog and pg_uncompresslog with Postgresql 8.4.2. Here's my configuration snippet: full_page_writes = on # recover from partial page writes wal_buffers = 256kB # min 32kB # (change requires restart) #wal_writer_delay = 200ms # 1-1 milliseconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 # - Checkpoints - checkpoint_segments = 64# in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables archive_command = 'test ! -f /dbms/pg_archive/%f.bz2 pg_compresslog %p | bzip2 - /dbms/pg_archive/%f.bz2' #command to use to archive a logfile segment All appears to be fine with the writes, and they are being saved off on the nightly backups without incident. I take a full dump using the instructions in the documentation and make sure I copy the proper must have file for consistency to be reached. The problem comes when I try to restore. recovery_conf contains: restore_command = '/usr/local/pgsql/recovery.sh %f %p' And that file contains: #! /bin/sh infile=$1 outfile=$2 if test -f /dbms/pg_archive/$infile.bz2 then bunzip2 -c /dbms/pg_archive/$infile.bz2 | /usr/local/pgsql/bin/pg_decompresslog - $outfile exit 0 else exit 1 fi == The problem is that it appears that some of the segments being saved are no good! On occasion I get this when trying to restore... Feb 7 12:43:51 dbms2 postgres[2001]: [210-1] LOG: restored log file 00010171009A from archive Feb 7 12:43:52 dbms2 postgres[2001]: [211-1] LOG: restored log file 00010171009B from archive Feb 7 12:43:52 dbms2 postgres[2001]: [212-1] LOG: restored log file 00010171009C from archive Feb 7 12:43:52 dbms2 postgres[2001]: [213-1] LOG: restored log file 00010171009D from archive Feb 7 12:43:53 dbms2 postgres[2001]: [214-1] LOG: restored log file 00010171009E from archive Feb 7 12:43:53 dbms2 postgres[2001]: [215-1] LOG: restored log file 00010171009F from archive Feb 7 12:43:54 dbms2 postgres[2001]: [216-1] LOG: restored log file 0001017100A0 from archive Feb 7 12:43:54 dbms2 postgres[2001]: [217-1] LOG: restored log file 0001017100A1 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [218-1] LOG: restored log file 0001017100A2 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [219-1] LOG: restored log file 0001017100A3 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [220-1] LOG: restored log file 0001017100A4 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [221-1] LOG: restored log file 0001017100A5 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [222-1] LOG: restored log file 0001017100A6 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [223-1] PANIC: corrupted page pointers: lower = 772, upper = 616, special = 0 Feb 7 12:43:57 dbms2 postgres[2001]: [223-2] CONTEXT: xlog redo hot_update: rel 1663/616245/1193269; tid 53/93; new 53/4 Feb 7 12:43:57 dbms2 postgres[2000]: [1-1] LOG: startup process (PID 2001) was terminated by signal 6: Abort trap Feb 7 12:43:57 dbms2 postgres[2000]: [2-1] LOG: terminating any other active server processes Eek. I assume this means that either A6 or A7 is corrupt. But I have the file both in the restore AND ON THE MACHINE WHERE IT ORIGINATED: On the SOURCE machine (which is running just fine): tickerforum# cksum *171*A[67]* 172998591 830621 0001017100A6.bz2 1283345296 1541006 0001017100A7.bz2 And off the BACKUP archive, which is what I'm trying to restore: # cksum *171*A[67]* 172998591 830621 0001017100A6.bz2 1283345296 1541006 0001017100A7.bz2
Re: [GENERAL] [HACKERS] Bug on pg_lesslog
Joshua D. Drake wrote: On Thu, 2010-02-11 at 23:39 +0900, Koichi Suzuki wrote: Dear Folks; A very serious bug was reported on pg_lesslog. So far, I found it's a bug in pg_compresslog. Please do not use pg_compresslog and pg_decompresslog until improved version is uploaded. I strongly advise to take base backup of your database. I apologize for inconvenience. I'll upload the new version ASAP. Should this go out on announce? I certainly think so. Anyone who gets caught by surprise on this could quite possibly lose all their data! I (fortunately) caught it during TESTING of my archives - before I needed them. -- Karl Denninger attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with pg_compresslog'd archives
This may belong in a bug report, but I'll post it here first... There appears to be a **SERIOUS** problem with using pg_compresslog and pg_uncompresslog with Postgresql 8.4.2. Here's my configuration snippet: full_page_writes = on # recover from partial page writes wal_buffers = 256kB # min 32kB # (change requires restart) #wal_writer_delay = 200ms # 1-1 milliseconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 # - Checkpoints - checkpoint_segments = 64# in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables archive_command = 'test ! -f /dbms/pg_archive/%f.bz2 pg_compresslog %p | bzip2 - /dbms/pg_archive/%f.bz2' #command to use to archive a logfile segment All appears to be fine with the writes, and they are being saved off on the nightly backups without incident. I take a full dump using the instructions in the documentation and make sure I copy the proper must have file for consistency to be reached. The problem comes when I try to restore. recovery_conf contains: restore_command = '/usr/local/pgsql/recovery.sh %f %p' And that file contains: #! /bin/sh infile=$1 outfile=$2 if test -f /dbms/pg_archive/$infile.bz2 then bunzip2 -c /dbms/pg_archive/$infile.bz2 | /usr/local/pgsql/bin/pg_decompresslog - $outfile exit 0 else exit 1 fi == The problem is that it appears that some of the segments being saved are no good! On occasion I get this when trying to restore... Feb 7 12:43:51 dbms2 postgres[2001]: [210-1] LOG: restored log file 00010171009A from archive Feb 7 12:43:52 dbms2 postgres[2001]: [211-1] LOG: restored log file 00010171009B from archive Feb 7 12:43:52 dbms2 postgres[2001]: [212-1] LOG: restored log file 00010171009C from archive Feb 7 12:43:52 dbms2 postgres[2001]: [213-1] LOG: restored log file 00010171009D from archive Feb 7 12:43:53 dbms2 postgres[2001]: [214-1] LOG: restored log file 00010171009E from archive Feb 7 12:43:53 dbms2 postgres[2001]: [215-1] LOG: restored log file 00010171009F from archive Feb 7 12:43:54 dbms2 postgres[2001]: [216-1] LOG: restored log file 0001017100A0 from archive Feb 7 12:43:54 dbms2 postgres[2001]: [217-1] LOG: restored log file 0001017100A1 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [218-1] LOG: restored log file 0001017100A2 from archive Feb 7 12:43:55 dbms2 postgres[2001]: [219-1] LOG: restored log file 0001017100A3 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [220-1] LOG: restored log file 0001017100A4 from archive Feb 7 12:43:56 dbms2 postgres[2001]: [221-1] LOG: restored log file 0001017100A5 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [222-1] LOG: restored log file 0001017100A6 from archive Feb 7 12:43:57 dbms2 postgres[2001]: [223-1] PANIC: corrupted page pointers: lower = 772, upper = 616, special = 0 Feb 7 12:43:57 dbms2 postgres[2001]: [223-2] CONTEXT: xlog redo hot_update: rel 1663/616245/1193269; tid 53/93; new 53/4 Feb 7 12:43:57 dbms2 postgres[2000]: [1-1] LOG: startup process (PID 2001) was terminated by signal 6: Abort trap Feb 7 12:43:57 dbms2 postgres[2000]: [2-1] LOG: terminating any other active server processes Eek. I assume this means that either A6 or A7 is corrupt. But I have the file both in the restore AND ON THE MACHINE WHERE IT ORIGINATED: On the SOURCE machine (which is running just fine): tickerforum# cksum *171*A[67]* 172998591 830621 0001017100A6.bz2 1283345296 1541006 0001017100A7.bz2 And off the BACKUP archive, which is what I'm trying to restore: # cksum *171*A[67]* 172998591 830621 0001017100A6.bz2 1283345296 1541006 0001017100A7.bz2 Identical, says the checksums. This is VERY BAD - if pg_compresslog is damaging the files in some instances then ANY BACKUP TAKEN USING THEM IS SUSPECT AND MAY NOT RESTORE!! Needless to say this is a MAJOR problem. -- Karl Denninger attachment: karl.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Retrieving performance information on a query
Is there a way through the libpq interface to access performance data on a query? I don't see an obvious way to do it - that is, retrieve the amount of time (clock, cpu, etc) required to process a command or query, etc Thanks in advance! -- -- Karl Denninger k...@denninger.net begin:vcard fn:Karl Denninger n:Denninger;Karl email;internet:k...@denninger.net x-mozilla-html:TRUE version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Whassup with this? (Create table xxx like yyy fails)
childrensjustice=# create table petition_bail like petition_white; ERROR: syntax error at or near like LINE 1: create table petition_bail like petition_white; Huh? Yes, the source table exists and obviously as postgres superuser (pgsql) I have select permission on the parent. -- Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.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] Whassup with this? (Create table xxx like yyy fails)
Douglas McNaught wrote: On Sat, Jul 19, 2008 at 9:02 PM, Karl Denninger [EMAIL PROTECTED] wrote: childrensjustice=# create table petition_bail like petition_white; ERROR: syntax error at or near like LINE 1: create table petition_bail like petition_white; It's not super-easy to see from the docs, but I think you need parentheses: CREATE TABLE petition_bail (LIKE petition_white); -Doug Aha. Thank you. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net
Re: [GENERAL] Redundant file server for postgres
What's the expected transaction split (read/write)? If mostly READs (e.g. SELECTs) then its very, very hard to do better from a performance perspective than Raid 1 with the transaction log on a separate array (physically separate spindles) I run a VERY busy web forum on a Quadcore Intel box with this setup and it is very fast. Really quite amazing when you get right down to it. The latest release of the PostgreSQL code markedly improved query optimization, by the way. The performance improvement when I migrated over was quite stunning. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net Robert Powell wrote: To whom it may concern, I'm looking for a file server that will give me a high level of redundancy and high performance for a postgres database. The server will be running only postgres as a backend service, connected to a front end server with the application on it. I was thinking along the lines of a RAID 10 setup with a very fast processor. Any suggestions would be greatly appreciated. Bob Powell The Hotchkiss School
Re: [GENERAL] Redundant file server for postgres
Craig Ringer wrote: Robert Powell wrote: To whom it may concern, I'm looking for a file server that will give me a high level of redundancy and high performance for a postgres database. For strong redundancy and availability you may need a secondary server and some sort of replication setup (be it a WAL-following warm spare, slony-I, or whatever). It depends on what you mean by high. As for performance - I'm still learning on this myself, so treat the following as being of questionable accuracy. As far as I know the general rule for databases is if in doubt, add more fast disks. A fast CPU (or depending on type of workload several almost-as-fast CPUs) will be nice, but if your database is big enough not to fit mostly in RAM you'll mostly be limited by disk I/O. To increase disk I/O performance, in general you want more disks. Faster disks will help, but probably not as much as just having more of them. More RAM is of course also nice, but might make a huge difference for some workloads and database types and relatively little for others. If doubling your RAM lets the server cache most of the database in RAM it'll probably speed things up a lot. If doubling the RAM is the difference between 2% and 4% of the DB in RAM ... it might not make such a difference (unless, of course, your queries mostly operate on a subset of your data that's fairly similar to your RAM size, you do lots of big joins, etc). Various RAID types also have implications for disk I/O. For example, RAID-5 tends to have miserable write performance. In the end, though, it depends a huge amount on your workload. Will you have huge numbers of simpler concurrent transactions, or relatively few heavy and complex ones? Will the database be read-mostly, or will it be written to very heavily? Vaguely how large is your expected dataset? Is all the data likely to be accessed with equal frequency or are most queries likely to concentrate on a small subset of the data? And so on... -- Craig Ringer The key issue on RAM is not whether the database will fit into RAM (for all but the most trivial applications, it will not) It is whether the key INDICES will fit into RAM. If they will, then you get a HUGE win in performance. If not, then it is all about disk I/O performance and the better you can spread that load across multiple spindles and get the data into the CPU at a very high rate of speed, the faster the system will perform. In terms of redundancy you have to know your workload before designing a strategy. For a database that is almost all queries (few inserts/updates) the job is considerably simpler than a database that sees very frequent inserts and/or updates. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.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] 8.2.6 8.3 blows up
Richard Huxton wrote: Karl Denninger wrote: The problem is that I was holding the ts_vector in a column in the table with a GIST index on that column. This fails horribly under 8.3; it appears to be ok on the reload but as there is a trigger on updates any update or insert fails immediately with a data mistype complaint. Are you sure you've not got an old definition of tsvector/tsquery or some such? I've got a tsvector column in a table, updated from a trigger with a gist index and it's fine. That's all created for 8.3 though, not dumped/restored from 8.2 Quite sure; I just did it by the book. The simple fix was to get rid of that and re-write the queries, which I've now done. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
I can reproduce this as I have the dump from before conversion and can load it on a different box and make it happen a second time. Would you like it on the list or privately? Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net Richard Huxton wrote: Karl Denninger wrote: Richard Huxton wrote: Karl Denninger wrote: The problem is that I was holding the ts_vector in a column in the table with a GIST index on that column. This fails horribly under 8.3; it appears to be ok on the reload but as there is a trigger on updates any update or insert fails immediately with a data mistype complaint. Are you sure you've not got an old definition of tsvector/tsquery or some such? I've got a tsvector column in a table, updated from a trigger with a gist index and it's fine. That's all created for 8.3 though, not dumped/restored from 8.2 Quite sure; I just did it by the book. The simple fix was to get rid of that and re-write the queries, which I've now done. Do you have a record of what the type error was, and what triggered it? If this is reproducable we'll need to change the docs. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 8.2.6 8.3 blows up
Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. Its not clear how I can have both versions running at once side-by-side; this is a production system and I can get a tee of the blowup and post it, but to do that I need to be able to start both versions at once. both ARE loaded on the system; is there a way to do that? Thanks in advance -- Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.6 8.3 blows up
A whole host of them, mostly about bad data formats in some of the table data. I suspect the underlying problem is that something got mangled in the table creates. I'm setting up on a different box as my attempt to create a second instance failed horribly - compiling with a different prefix and port number appears to work but when you do an initdb it blows up with a complaint about not being able to create the semaphores and shared segment. It appears the port number is used for the SEMID and SHMID prefixes, and those for some reason are not getting reset (it may be that the change in configure requires a gmake clean; not sure) In any event I have another machine and will get something more detailed ASAP - I will also try the restore program and see if that works. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net Scott Marlowe wrote: On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] wrote: Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. So what error message? ---(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 %SPAMBLOCK-SYS: Matched [hub.org+], message ok
Re: [GENERAL] 8.2.6 8.3 blows up
Scott Marlowe wrote: On Sun, Mar 2, 2008 at 1:41 PM, Karl Denninger [EMAIL PROTECTED] wrote: Ugh. I am attempting to move from 8.2.6 to 8.3, and have run into a major problem. The build goes fine, the install goes fine, the pg_dumpall goes fine. However, the reload does not. I do the initdb and then during the reload I get thousands of errors, apparently from table data which is perfectly ok in the tables, but pukes up a hairball when attempted to be reloaded. So what error message? It looks like the problem had to do with the tsearch2 module that I have in use in a number of my databases, and which had propagated into template1, which meant that new creates had it in there. If its in a database in a dump it trashes the restore for everything beyond that point when the restore is unable to find it in the new database. I went through the entire catalog and pulled the old contrib/tesearch2 module in for those databases where it was present before, and it now appears to be restoring ok. I'm not quite clear what I have to do in terms of if/when I can drop the old tsearch config stuff and for obvious reasons (like not running into this in the future) I'd like to. Can I just run the untsearch2 script against those databases or will that destroy the search functionality? Are there changes necessary in the SQL code (the documentation implies not unless I'm doing odd things) Thanks in advance... Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.6 8.3 blows up
Tom Lane wrote: Karl Denninger [EMAIL PROTECTED] writes: It looks like the problem had to do with the tsearch2 module that I have in use in a number of my databases, and which had propagated into template1, which meant that new creates had it in there. The old tsearch2 module isn't at all compatible with 8.3. I'd suggest removing it from the 8.2 source databases where possible, ie wherever you're not actually using it. Where you are using it, the cure is to install the 8.3 version of contrib/tsearch2 into the target database *before* restoring. See http://www.postgresql.org/docs/8.3/static/textsearch-migration.html http://www.postgresql.org/docs/8.3/static/tsearch2.html regards, tom lane %SPAMBLOCK-SYS: Matched [postgresql.org], message ok Yeah, I read that in the docs... But there are compatability problems with the tsearch2 contrib module in 8.3 and backwards constructs and code, with the most serious being that it simply doesn't work correctly for some of the older ways of formatting queries and storage. Specifically, if you store the ts_vector in the table via a different column and have a GIST index on it, this fails with the 8.3 tsearch2 module loaded as all updates or inserts return a complaint when the trigger fires - the claim is that there's a data type mismatch. The simplest fix is to rework the index to go directly off the underlying column and then query off that but that requires a change to both the query and the schema. Not a big deal to do once you figure out what's up, but it DOES require code changes. See my other message to the list on this; I have worked around it in the applications affected but this is likely to bite people with some authority until they get their arms around it. Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.6 8.3 blows up
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, 02 Mar 2008 15:46:25 -0600 Karl Denninger [EMAIL PROTECTED] wrote: I'm not quite clear what I have to do in terms of if/when I can drop the old tsearch config stuff and for obvious reasons (like not running into this in the future) I'd like to. Can I just run the untsearch2 script against those databases or will that destroy the search functionality? Are there changes necessary in the SQL code (the documentation implies not unless I'm doing odd things) One of the goals for 8.3 and the integrated tsearch was to remove exactly this problem. Sincerely, Joshua D. Drake Unfortunately there are code changes necessary on my end in one of my major applications; the attempt at compatability is somewhat less than successful from here. The problem is that I was holding the ts_vector in a column in the table with a GIST index on that column. This fails horribly under 8.3; it appears to be ok on the reload but as there is a trigger on updates any update or insert fails immediately with a data mistype complaint. The fix is to rejigger the query to go directly at the fields and build a gin or gist index directly on the underlying, which is not difficult but DOES require code and schema changes. I'll get through it but this is going to bite people with some authority if they have applications that were doing things the same way I was - there was nothing esoteric about the way I had coded it (although it could be argued it was somewhat wasteful of disk space.) -- Karl Denninger [EMAIL PROTECTED]
[GENERAL] Question regarding autovacuum
Running 8.2.4. The following is in my postgresql.conf: # - Query/Index Statistics Collector - #stats_command_string = on update_process_title = on stats_start_collector = on # needed for block or row stats # (change requires restart) #stats_block_level = off stats_row_level = on #stats_reset_on_server_start = off # (change requires restart) # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off #--- # AUTOVACUUM PARAMETERS #--- autovacuum = on # enable autovacuum subprocess? # 'on' requires stats_start_collector # and stats_row_level to also be on autovacuum_naptime = 10min # time between autovacuum runs #autovacuum_vacuum_threshold = 500 # min # of tuple updates before autovacuum_vacuum_threshold = 200 # min # of tuple updates before # vacuum autovacuum_analyze_threshold = 125 # min # of tuple updates before #autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before autovacuum_vacuum_scale_factor = 0.1# fraction of rel size before # vacuum autovacuum_analyze_scale_factor = 0.05 # fraction of rel size before #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze autovacuum_freeze_max_age = 2 # maximum XID age before forced vacuum # (change requires restart) autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit How do I know if the autovacuum is actually running? The reason I believe its not - the database in question is being hit VERY HARD with both updates and queries. Its a forum, and there are updates on essentially every access (user's IP address is updated, time last touched the account is updated, etc) Anyway, after anywhere from a few hours to a day or so, performance goes straight in the toilet. The system starts thrashing the disk hard - indicating that there's a major problem trying to keep the working set in memory; if not caught quickly it deteriorates to the point that access time rises so that the maximum connection limit is hit and then users get Dbms connection errors (while the load average goes sky-high as well and disk I/O is pinned). A manual Vacuum full analyze fixes it immediately. But... .shouldn't autovacuum prevent this? Is there some way to look in a log somewhere and see if and when the autovacuum is being run - and on what? -- Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question regarding autovacuum
I don't know. How do I check? Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net Alvaro Herrera wrote: Karl Denninger wrote: A manual Vacuum full analyze fixes it immediately. But... .shouldn't autovacuum prevent this? Is there some way to look in a log somewhere and see if and when the autovacuum is being run - and on what? Are your FSM settings enough to keep track of the dead space you have?
Re: [GENERAL] Question regarding autovacuum
Tom Lane wrote: Karl Denninger [EMAIL PROTECTED] writes: But... .shouldn't autovacuum prevent this? Is there some way to look in a log somewhere and see if and when the autovacuum is being run - and on what? There's no log messages (at the default log verbosity anyway). But you could look into the pg_stat views for the last vacuum time for each table. regards, tom lane It looks like it IS being run. Now the problem is, what's going on? Maybe running out of fsm_map entries? Hmnm. vacuum full fixes it, which I assume reclaims those, yes? I wonder if increasing that quite radically (I have lots of server RAM and SYSV SHM has been cranked wide) would take care of it -- Karl
Re: [GENERAL] Question regarding autovacuum
Steve Crawford wrote: Karl Denninger wrote: Are your FSM settings enough to keep track of the dead space you have? I don't know. How do I check? vacuum verbose; Toward the bottom you will see something like: ... 1200 page slots are required to track all free space. Current limits are: 453600 page slots, 1000 relations, using 2723 kB. Make sure your current limits have a higher number than the page slots required. Cheers, Steve Am I correct in that this number will GROW over time? Or is what I see right now (with everything running ok) all that the system will ever need? If the latter, then I'm WELL within limits and I guess I need to tune the autovacuum parameters to be more aggressive; system views show it IS being run. INFO: free space map contains 5895 pages in 639 relations DETAIL: A total of 14976 page slots are in use (including overhead). 14976 page slots are required to track all free space. Current limits are: 179200 page slots, 1000 relations, using 1115 kB. -- Karl Denninger
Re: [GENERAL] Question regarding autovacuum
Scott Marlowe wrote: On 8/28/07, Karl Denninger [EMAIL PROTECTED] wrote: Am I correct in that this number will GROW over time? Or is what I see right now (with everything running ok) all that the system will ever need? They will grow at first to accomodate your typical load of dead tuples created between regular vacuums. Then they should reach a steady state where they will slowly grow as your activity levels increase. So it's a good idea to allocate 20 to 50% more than what vacuum verbose says you'll need for overhead. also keep in mind that vacuum verbose only tells you what the one db in the server needs. If you have multiple dbs in your postgresql service, you'll need to run vacuum verbose on all of them after X time (typical time between your vacuums) and add the needed free space together to get the total needed. If the latter, then I'm WELL within limits and I guess I need to tune the autovacuum parameters to be more aggressive; system views show it IS being run. INFO: free space map contains 5895 pages in 639 relations DETAIL: A total of 14976 page slots are in use (including overhead). 14976 page slots are required to track all free space. Current limits are: 179200 page slots, 1000 relations, using 1115 kB. Yeah, that looks good. Note that the preferred state for pgsql is to have 10-25% free space in frequently updated tables, rather than removing it all with reindex / vacuum full. This keeps the files from getting fragmented AND keeps the OS from having to constantly allocate more space for the tables. Just cron up something to run vacuum verbose everynight and email it to you to peruse over coffee in the morning, and compare to previous nights. that'll give you an idea of how you're fsm is holding up. That implies, however, that I need to make autovacuum more aggressive - in other words, it means that in all probability the fsm maps are not the problem. What I have noticed is that after a half-day or so of normal use the system get notably slower on the same queries, but a vacuum full analyze puts it right back to where it was. So SOMETHING is getting clogged up... -- Karl
[GENERAL] Select question..... is there a way to do this?
Assume the following tables: Table ITEM (user text, subject text, number integer, changed timestamp); table SEEN (user text, number integer, lastviewed timestamp); Ok, now the data in the SEEN table will have one tuple for each user and number in the table ITEM which a user has viewed, and the last time they looked at it. It will also have one entry per user with a NULL number, which will be written to the table when the user scans the table and finds no new records (to keep the SEEN table from becoming exponentially large as otherwise it would require USER X ITEMs tuples!); that record marks the last time the user was there and there were no newer ITEMs. If I do the following query: select item.user, item.subject, item.number from item, seen where (item.user = seen.user) and (item.number = seen.number) and (item.changed seen.lastviewed); I get all items which have a record in SEEN. So far so good. But what I want is all items which EITHER have (1) a record in SEEN which matches (and is older) OR which are NEWER than the SEEN record with a NULL number. That I'm having trouble doing. The following does not do what I want: select item.user, item.subject, item.number from item, seen where (item.user = seen.user and item.number = seen.number and item.changed seen.lastviewed) OR (item.user = seen.user and item.changed seen.lastviewed and seen.number is null); That second query returns TWO entries for a SEEN record (which I can control out with Select distinct) BUT it breaks in another nasty way - if I have an item that has a CHANGED time that is later than the null record, *IT GETS SELECTED EVEN IF IT HAS A DISTINCT RECORD*. That's no good. Ideas? (Attempted to use AND NOT as a conditional on the second clause to the OR and that didn't work; it excluded all of the NULL records) -- Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Upgrade from 7.4 - 8.0.1 - problem with dump/restore
Hi folks; Trying to move from 7.4.1 to 8.0.1 All goes well until I try to reload after installation. Dump was done with the 8.0.1 pg_dumpall program On restore, I get thousands of errors on the console, and of course the data doesn't end up back in the system. The only two non-standard things about my 7.4.1 DBMS is that I do have significant amonuts of binary data stored in the dbms itself, and in addition I have tsearch loaded. Any ideas? -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Upgrade from 7.4 - 8.0.1 - problem with dump/restore
On Thu, Feb 03, 2005 at 01:03:57PM -0600, Karl Denninger wrote: Hi folks; Trying to move from 7.4.1 to 8.0.1 All goes well until I try to reload after installation. Dump was done with the 8.0.1 pg_dumpall program On restore, I get thousands of errors on the console, and of course the data doesn't end up back in the system. The only two non-standard things about my 7.4.1 DBMS is that I do have significant amonuts of binary data stored in the dbms itself, and in addition I have tsearch loaded. Any ideas? PS: The binary data is in a BYTEA field, not a BLOB. It looks ok in the dump file. -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Eeek! Major problem after reload with tsearch2
Ok, I found out what was going on with the tsearch2 module and reloading after an upgrade to 8.0.1 from 7.4.1 The data now loads cleanly, and selects are fine. HOWEVER, when I attempt an update, I issue the following SQL command (this table has a tsearch2 vector in it:) update post set invisible='0' where ordinal='2843' And get back: ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by lt.tokid desc; Ai! A reindex did nothing. What did I miss? Looks like there's something missing, but what?! -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Eeek! Major problem after reload with tsearch2
If I remove the full-text indexing from the table involved (remove the indices and triggers) then the system is fine. Somehow, it appears that there's some kind of internal consistency problem with the tsearch2 package. Attempts to recreate the indices (after dropping them) fail with the same error; it looks like something is badly mangled internally in the tsearch2 module... even though it DOES appear that it loaded properly. -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind On Thu, Feb 03, 2005 at 02:37:04PM -0600, Karl Denninger wrote: Ok, I found out what was going on with the tsearch2 module and reloading after an upgrade to 8.0.1 from 7.4.1 The data now loads cleanly, and selects are fine. HOWEVER, when I attempt an update, I issue the following SQL command (this table has a tsearch2 vector in it:) update post set invisible='0' where ordinal='2843' And get back: ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by lt.tokid desc; Ai! A reindex did nothing. What did I miss? Looks like there's something missing, but what?! -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Eeek! Major problem after reload with tsearch2
The patch is in the 8.0.1 version of Tsearch2 already. The problem is that I have a dump from a 7.4.1 database taken with the 8.0.1 pg_dumpall that I need to be able to get back online on 8.0.x. Is the only option to find all the functions in the tsearch.sql file, drop them by hand, remove all the tsearch2 index fields, then reload tsearch2.sql and re-create the indices? That's not impossible, but a shizload of work, as there's no good way that I can see to drop all the tsearch2 functions in a single step (e.g. I'd have to go through the tsearch2.sql file individually, find all the entries, drop them, etc.) Another possibility Does a pg_dumpall dump functions as well? It appears not from the documentation - so if I drop the columns and then dump the database, I should have a clean dump without the OID stuff in it. If I then re-init and reload the data, I should then be able to do so without the tsearch2.sql stuff. I can then reload the tsearch2.sql functions and re-create the indices. Sound plausible? - -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind On Fri, Feb 04, 2005 at 12:23:02AM +0300, Oleg Bartunov wrote: This is know issue with OIDS. You,probably, needed to apply regprocedure_7.4.patch.gz patch from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Oleg On Thu, 3 Feb 2005, Karl Denninger wrote: Ok, I found out what was going on with the tsearch2 module and reloading after an upgrade to 8.0.1 from 7.4.1 The data now loads cleanly, and selects are fine. HOWEVER, when I attempt an update, I issue the following SQL command (this table has a tsearch2 vector in it:) update post set invisible='0' where ordinal='2843' And get back: ERROR: cache lookup failed for function 36476 CONTEXT: SQL statement select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by lt.tokid desc; Ai! A reindex did nothing. What did I miss? Looks like there's something missing, but what?! -- Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Problem resolved (tsearch2 inhibiting migration)
Got it fixed. As it happens, there's an untsearch2.sql script in the contrib directory. Removing the transaction block around it allowed it to kill all the dregs from the 7.4.x database entries (on 8.0.1) and a subsequent tsearch2.sql was then able to rebuild them. I had to reinsert the columns and indices, but that's not a big deal. All fixed... thanks to the pointer to the OID issue, that got me on the right track. -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Problem resolved (tsearch2 inhibiting migration)
On Thu, Feb 03, 2005 at 06:59:55PM -0700, Michael Fuhr wrote: On Thu, Feb 03, 2005 at 06:44:55PM -0600, Karl Denninger wrote: As it happens, there's an untsearch2.sql script in the contrib directory. That reminds me: it would be useful if all contributed modules had an unmodule.sql file. That would simplify reloading the module if the definitions changed, as recently happened when STRICT was added to functions in chkpass and a few other modules; it would also make it easy to remove the module from a particular database if it were no longer needed. Or is there already a way of doing this that doesn't require you to figure out the DROP statements yourself? Running gmake uninstall in the module's source directory only removes the .so, .sql, and other installed files -- it doesn't DROP any objects that have been created in databases. No, there is no good way to do that. What's worse, is that if it goes into the template directory, it pollutes every database on the system from that point forward, so you have to back it out of each in turn I agree with this - what would be even better would be a way to create 'subclasses' for things like this, which could then be 'included' easily. I don't know if the latter is possible - I haven't seen an obvious way to do that, but there may be a way. -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problem resolved (tsearch2 inhibiting migration)
On Thu, Feb 03, 2005 at 10:20:47PM -0500, Tom Lane wrote: Karl Denninger [EMAIL PROTECTED] writes: I agree with this - what would be even better would be a way to create 'subclasses' for things like this, which could then be 'included' easily. We could decree that a contrib module's script should create a schema and shove everything it makes into that schema. Then DROP SCHEMA CASCADE is all you need to get rid of it. However, you'd probably end up having to add this schema to your search path to use the module conveniently. regards, tom lane I would prefer that vastly over what I had to deal with this time. Until I discovered the untsearch2.sql script I was seriously considering the trouble of backing this out BY HAND either in a dump or in the online database. Either would have been a stupendous amount of work. Please consider this change in approach - someone else has to have been bit in the butt by this one other than me by now. -- -- Karl Denninger ([EMAIL PROTECTED]) Internet Consultant Kids Rights Activist http://www.denninger.netMy home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.comMusings Of A Sentient Mind ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
