[ADMIN] create foreign key without checking
Is there a way to make inserts int the systemtables to create a foreign key without checks I have archive tables to put in the database - and then data will be inserted afterwards to that table I want to switch off foreign keys for the import and activate them after the import but without checking The data matches the foreign keys which I import Is this possible? -- Ewald Geschwinde
[ADMIN] sending data with COPY SQL command, libpq and structure of SELECT resulting buffer
I sent this message to INTERFACES, but there is no answer, maybe on ADMIN list somebody knows something about it... From the docs i understood, that with COPY SQL command we should use for synchr. sending: PQexec PQResultstatus returns PGRES_COPY_OUT PQputCopyData PQputCopyEnd PQResultstatus returns PGRES_COMMAND_OK How should look the structure of buffer in int PQputCopyData(PGconn *conn,const char *buffer,int nbytes); id for me clear (it should be the same like for example in text dumps of tables done with pg_dump). The question is: Can I in function: int PQputCopyData(PGconn *conn,const char *buffer,int nbytes); use as a result buffer directly given by using PQexec for SELECT SQL command ? In other way: Is the structure given as result of PQexec with SELECT SQL command directly usable as buffer for PQputCopyData ? == Brgds Adam ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Problem with PITR Past Particular WAL File
Tom: Thanks for your help on this. The trace details are as follows: Core was generated by `/usr/local/pgsql/bin/postgres -D /pgdata01/data'. Program terminated with signal 11, Segmentation fault. #0 0x080b8ee0 in entrySplitPage () #1 0x080baccf in ginInsertValue () #2 0x080b81b7 in gin_xlog_cleanup () #3 0x080af4ce in StartupXLOG () #4 0x080c04ca in BootstrapMain () #5 0x08186b2f in StartChildProcess () #6 0x081889eb in PostmasterMain () #7 0x0814ee9e in main () Cheers, -craig --- Craig A. McElroy Contegix Beyond Managed Hosting(r) for Your Enterprise On Oct 24, 2007, at 1:26 PM, Tom Lane wrote: Craig McElroy <[EMAIL PROTECTED]> writes: Can you get a stack trace from the core dump reported here? Certainly, how can that be obtained? $ gdb /path/to/postgres-executable /path/to/core-file gdb> bt gdb> quit If you don't find a corefile in $PGDATA (or wherever your system puts core files) then you probably need to restart the postmaster with "ulimit -c unlimited" to allow producing a core. If the "bt" output is just numbers and no symbols then it won't be of any use; in that case you'll need to find or build non-stripped Postgres executables. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Problem with PITR Past Particular WAL File
Craig McElroy wrote: > Tom: > Thanks for your help on this. The trace details are as follows: > > Core was generated by `/usr/local/pgsql/bin/postgres -D /pgdata01/data'. > Program terminated with signal 11, Segmentation fault. > > #0 0x080b8ee0 in entrySplitPage () > #1 0x080baccf in ginInsertValue () > #2 0x080b81b7 in gin_xlog_cleanup () So it looks like a bug in the gin xlog code, which makes sense because it's new. Can you please execute in that gdb session the command "bt full" and send the output? -- Alvaro Herrerahttp://www.advogato.org/person/alvherre "La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Problem with PITR Past Particular WAL File
Alvaro: I just tried, but I only get "No symbol table info available." after each line of the trace. Cheers, -craig --- Craig A. McElroy Contegix Beyond Managed Hosting(r) for Your Enterprise On Oct 25, 2007, at 7:57 AM, Alvaro Herrera wrote: Craig McElroy wrote: Tom: Thanks for your help on this. The trace details are as follows: Core was generated by `/usr/local/pgsql/bin/postgres -D /pgdata01/ data'. Program terminated with signal 11, Segmentation fault. #0 0x080b8ee0 in entrySplitPage () #1 0x080baccf in ginInsertValue () #2 0x080b81b7 in gin_xlog_cleanup () So it looks like a bug in the gin xlog code, which makes sense because it's new. Can you please execute in that gdb session the command "bt full" and send the output? -- Alvaro Herrerahttp://www.advogato.org/ person/alvherre "La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)
[ADMIN] Suse Linux Postgres
Hello Frnds, I am using the Postgres on Linux, its the default that gets installed with the Suse Linux. Now when i run the psql command it ask for passwordI have created a role named "abccc" now i want to assign it a password..how to do it in Linux. before trying this I was trying to install the Pl/java but it was giving problem that is authentication failed for the USer "abccc" if i give the password as i have created the "abccc" role without password...by using command createuser abbccc. If i remove the Password option then it ask that,,, Postgres option is not without password(something like this not exact)...Then i tried to alter the user to set the password for it...and face the following problem.. any kind of suggestion would be appreciated ... Best Regards, Yogesh Arora. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Problem with PITR Past Particular WAL File
Craig McElroy wrote: > Alvaro: > I just tried, but I only get "No symbol table info available." after each > line of the trace. Well, we have two possibilities: 1. you send us a complete, reproducible test case, 2. you recompile with debugging enabled (configure --enable-debug) to get a more useful backtrace -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Suse Linux Postgres
On 10/25/07, yogesh <[EMAIL PROTECTED]> wrote: > Hello Frnds, > I am using the Postgres on Linux, its the default that gets > installed with the Suse Linux. > Now when i run the psql command it ask for passwordI have created > a role named "abccc" > now i want to assign it a password..how to do it in > Linux. > > before trying this I was trying to install the Pl/java but it was > giving problem that is > authentication failed for the USer "abccc" if i give the > password as i have created the "abccc" role without > password...by using command > > createuser abbccc. > > If i remove the Password option then it ask that,,, > Postgres option is not without password(something like this not > exact)...Then i tried to alter the user to set the password for > it...and face the following problem.. > > > any kind of suggestion would be appreciated ... http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Problem with PITR Past Particular WAL File
Craig McElroy <[EMAIL PROTECTED]> writes: > Core was generated by `/usr/local/pgsql/bin/postgres -D /pgdata01/data'. > Program terminated with signal 11, Segmentation fault. > #0 0x080b8ee0 in entrySplitPage () > #1 0x080baccf in ginInsertValue () > #2 0x080b81b7 in gin_xlog_cleanup () > #3 0x080af4ce in StartupXLOG () > #4 0x080c04ca in BootstrapMain () > #5 0x08186b2f in StartChildProcess () > #6 0x081889eb in PostmasterMain () > #7 0x0814ee9e in main () Hm, I wonder if this is explained by a bug already fixed in 8.2.5: 2007-06-04 11:59 teodor * src/backend/access/gin/: gindatapage.c, ginentrypage.c, ginget.c, ginvacuum.c, ginxlog.c (REL8_2_STABLE): Fix bundle bugs of GIN: - Fix possible deadlock between UPDATE and VACUUM queries. Bug never was observed in 8.2, but it still exist there. HEAD is more sensitive to bug after recent "ring" of buffer improvements. - Fix WAL creation: if parent page is stored as is after split then incomplete split isn't removed during replay. This happens rather rare, only on large tables with a lot of updates/inserts. - Fix WAL replay: there was wrong test of XLR_BKP_BLOCK_* for left page after deletion of page. That causes wrong rightlink field: it pointed to deleted page. - add checking of match of clearing incomplete split - cleanup incomplete split list after proceeding All of this chages doesn't change on-disk storage, so backpatch... But second point may be an issue for replaying logs from previous version. Teodor, can you comment on whether this stack trace looks like it could be related to that fix? Craig, can you retry your test scenario on 8.2.5? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Problem with PITR Past Particular WAL File
#0 0x080b8ee0 in entrySplitPage () #1 0x080baccf in ginInsertValue () Corrupted stack? gin_xlog_cleanup() doesn't call ginInsertValue() directly. #2 0x080b81b7 in gin_xlog_cleanup () #3 0x080af4ce in StartupXLOG () #4 0x080c04ca in BootstrapMain () #5 0x08186b2f in StartChildProcess () #6 0x081889eb in PostmasterMain () #7 0x0814ee9e in main () Teodor, can you comment on whether this stack trace looks like it could be related to that fix? No, I suppose. That changes was about deadlock prevention, recognize splits and fixes in deletion of page in a posting tree. entrySplitPage() is a split of page of indexed values. Hmm. I suppose, I found a reason. Real backtrace should look like: gin_xlog_cleanup() ginContinueSplit() prepareEntryScan(,,,NULL) // makes btree.ginstate = NULL ginInsertValue() entrySplitPage() // tries to access // btree->ginstate->tupdesc in // 497 line That piece of code: value = index_getattr(leftrightmost, FirstOffsetNumber, btree->ginstate->tupdesc, &isnull); btree->entry = GinFormTuple(btree->ginstate, value, NULL, 0); ItemPointerSet(&(btree->entry)->t_tid, BufferGetBlockNumber(lbuf), InvalidOffsetNumber); It just makes new tuple with the same value and another ItemPointer. Some later I'll make a patch which will not call index_getattr() and GinFormTuple() - now I know how make a test suite. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] Database Restore - time of last occurrence
Hello all, Is there a way to tell when was the LAST time a database was restored? I checked the pg_database system table but I'm not sure what 'datfrozenxid' means or if it means anything SELECT datname, age(datfrozenxid) FROM pg_database; Thanks...Michelle. -- View this message in context: http://www.nabble.com/Database-Restore---time-of-last-occurrence-tf4692718.html#a13413046 Sent from the PostgreSQL - admin mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Postgresql New login - password expiration???
Hello all, I've created a new Postgresql login and I set an expiration date on the account 'VALID until xx-xx-xxx'...will this require the user to CHANGE his password at expiration or will it DISABLE the user account??? Also, how do I set password expiration on new accounts??? ---I want them to CHANGE their password upon first logging in... Thanks...Michelle. -- View this message in context: http://www.nabble.com/Postgresql-New-login---password-expirationtf4692732.html#a13413089 Sent from the PostgreSQL - admin mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Problem with PITR Past Particular WAL File
Teodor: Thanks for the followup. We will be upgrading this system to 8.2.5 in the next day or so and will test it on that anyways just to be sure it isn't related. Let me know if there is anything else we can provide to help track down the bug. We will be keeping this base backup and WAL files around so that we can use them for testing. Cheers, -craig --- Craig A. McElroy Contegix Beyond Managed Hosting(r) for Your Enterprise On Oct 25, 2007, at 1:05 PM, Teodor Sigaev wrote: #0 0x080b8ee0 in entrySplitPage () #1 0x080baccf in ginInsertValue () Corrupted stack? gin_xlog_cleanup() doesn't call ginInsertValue() directly. #2 0x080b81b7 in gin_xlog_cleanup () #3 0x080af4ce in StartupXLOG () #4 0x080c04ca in BootstrapMain () #5 0x08186b2f in StartChildProcess () #6 0x081889eb in PostmasterMain () #7 0x0814ee9e in main () Teodor, can you comment on whether this stack trace looks like it could be related to that fix? No, I suppose. That changes was about deadlock prevention, recognize splits and fixes in deletion of page in a posting tree. entrySplitPage() is a split of page of indexed values. Hmm. I suppose, I found a reason. Real backtrace should look like: gin_xlog_cleanup() ginContinueSplit() prepareEntryScan(,,,NULL) // makes btree.ginstate = NULL ginInsertValue() entrySplitPage() // tries to access // btree->ginstate->tupdesc in // 497 line That piece of code: value = index_getattr(leftrightmost, FirstOffsetNumber, btree- >ginstate->tupdesc, &isnull); btree->entry = GinFormTuple(btree->ginstate, value, NULL, 0); ItemPointerSet(&(btree->entry)->t_tid, BufferGetBlockNumber (lbuf), InvalidOffsetNumber); It just makes new tuple with the same value and another ItemPointer. Some later I'll make a patch which will not call index_getattr() and GinFormTuple() - now I know how make a test suite. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http:// www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] 32-bit to 64-bit migration screwup
I'm assuming you've made a jump in database version - ie, 7.4 to 8.2 or similar. You need to use initdb to create new database files, then pg_dumpall on the old system to dump the data out of the old before importing to the new. Make sure you use pg_dumpall from the new version to dump the data from the old database. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Young Sent: Friday, 26 October 2007 15:52 To: pgsql-admin@postgresql.org Subject: [ADMIN] 32-bit to 64-bit migration screwup All, I was hoping someone can help me fix a big blunder. I recently bought a new system to replace the old one. The new system is running 64bit linux and the old one 32bit. I installed PG 64-bit and tried to bring the old database back up. Even though I'm running the same version of PG, it is giving me an error when I try to start it back up. below is the message from pgstartup.log. It looks like it doesn't recognize the control file anymore. Am I screwed? I don't have the old system anymore and I didn't do a dump. I figure if I'm running the same version of PG, the data will come back up correctly. If someone can help me out of this jam, I would greatly appreciate it. Thanks, David --pgstartup.log--- The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. fixing permissions on existing directory /var/lib/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 24MB/153600 creating configuration files ... ok creating template1 database in /var/lib/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok Success. You can now start the database server using: /usr/bin/postgres -D /var/lib/pgsql/data or /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start LOG: logger shutting down LOG: logger shutting down LOG: logger shutting down LOG: logger shutting down LOG: logger shutting down FATAL: incorrect checksum in control file FATAL: incorrect checksum in control file FATAL: incorrect checksum in control file THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.
Re: [ADMIN] 32-bit to 64-bit migration screwup
Hi, On Thu, 2007-10-25 at 22:52 -0700, David Young wrote: > FATAL: incorrect checksum in control file Did you move the old data files to the new server? If so, it won't work. Dump your 32-bit database with 32-bit binaries, and load that data to your 64-bit database. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[ADMIN] 32-bit to 64-bit migration screwup
All, I was hoping someone can help me fix a big blunder. I recently bought a new system to replace the old one. The new system is running 64bit linux and the old one 32bit. I installed PG 64-bit and tried to bring the old database back up. Even though I'm running the same version of PG, it is giving me an error when I try to start it back up. below is the message from pgstartup.log. It looks like it doesn't recognize the control file anymore. Am I screwed? I don't have the old system anymore and I didn't do a dump. I figure if I'm running the same version of PG, the data will come back up correctly. If someone can help me out of this jam, I would greatly appreciate it. Thanks, David --pgstartup.log--- The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. fixing permissions on existing directory /var/lib/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 24MB/153600 creating configuration files ... ok creating template1 database in /var/lib/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok Success. You can now start the database server using: /usr/bin/postgres -D /var/lib/pgsql/data or /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start LOG: logger shutting down LOG: logger shutting down LOG: logger shutting down LOG: logger shutting down LOG: logger shutting down FATAL: incorrect checksum in control file FATAL: incorrect checksum in control file FATAL: incorrect checksum in control file
