Re: [ADMIN] Catching up Production from Warm Standby after maintenance - Please help
On Mon, Jul 6, 2009 at 8:38 PM, Jennifer Spencer wrote: > Hello All - > > I will soon be receiving a new production and warm-standby machine. Prior > to now, we have only had one database machine and no warm standby. > > We had planned to feed the standby with one backup and then use WAL files > rsync'd over until either catastrophe or maintenance required on the primary > machine. I will be able to properly close out my users on the primary, > force a closeout of the log file (pg_switch_xlog), and shut it down. Then I > can catch up and ingest the last WAL on my warm standby, let the users in, > and have the warm standby become the new primary. All okay so far. > > But. When the primary is vacuumed, re-indexed and all clean and shiny > again, HOW do I catch up with data changes that happened in the meantime on > my warm standby without corruption or >30 minutes of user down-time? I > cannot re-ingest WALs from the warm standby into the cleaned up primary or I > get a PANIC, and I don't have time to run a full backup on the warm standby > and ingest it into the primary leaving everything down. If you've moved on, so to speak, with the new primary, you restart the old primary, now warm standby, the same way you initially created the warm standby. issue the start hot backup command to the primary, copy over all the data dir and start shipping WAL files to it before you start continuous recovery. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Catching up Production from Warm Standby after maintenance - Please help
Hello All - I will soon be receiving a new production and warm-standby machine. Prior to now, we have only had one database machine and no warm standby. We had planned to feed the standby with one backup and then use WAL files rsync'd over until either catastrophe or maintenance required on the primary machine. I will be able to properly close out my users on the primary, force a closeout of the log file (pg_switch_xlog), and shut it down. Then I can catch up and ingest the last WAL on my warm standby, let the users in, and have the warm standby become the new primary. All okay so far. But. When the primary is vacuumed, re-indexed and all clean and shiny again, HOW do I catch up with data changes that happened in the meantime on my warm standby without corruption or >30 minutes of user down-time? I cannot re-ingest WALs from the warm standby into the cleaned up primary or I get a PANIC, and I don't have time to run a full backup on the warm standby and ingest it into the primary leaving everything down. I know some of you must have tackled this before, so I am really hoping you can help me. I checked the archives but didn't see anything about how to recover the primary after the repair or maintenance had been completed. I was really hoping to use the logs on the warm standby to feed into the cleaned up primary to catch up, but it seems that's a hopeless idea since any vacuuming or other cleaning I may do changes my binary data files beyond the WAL's recognition. Thanks in advance for your assistance. Best regards, Jennifer Spencer _ Hotmail® has ever-growing storage! Don’t worry about storage limits. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
[ADMIN] Catching up Production from Warm Standby after maintenance - Please help
Hello All - I will soon be receiving a new production and warm-standby machine. Prior to now, we have only had one database machine and no warm standby. We had planned to feed the standby with one backup and then use WAL files rsync'd over until either catastrophe or maintenance required on the primary machine. I will be able to properly close out my users on the primary, force a closeout of the log file (pg_switch_xlog), and shut it down. Then I can catch up and ingest the last WAL on my warm standby, let the users in, and have the warm standby become the new primary. All okay so far. But. When the primary is vacuumed, re-indexed and all clean and shiny again, HOW do I catch up with data changes that happened in the meantime on my warm standby without corruption or >30 minutes of user down-time? I cannot re-ingest WALs from the warm standby into the cleaned up primary or I get a PANIC, and I don't have time to run a full backup on the warm standby and ingest it into the primary leaving everything down. I know some of you must have tackled this before, so I am really hoping you can help me. I checked the archives but didn't see anything about how to recover the primary after the repair or maintenance had been completed. I was really hoping to use the logs on the warm standby to feed into the cleaned up primary to catch up, but it seems that's a hopeless idea since any vacuuming or other cleaning I may do changes my binary data files beyond the WAL's recognition. Thanks in advance for your assistance. Best regards, Jennifer Spencer _ Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290
Re: [ADMIN] Compile errors when building 32-bit on 64-bit system
FWIW, it does appear that 8.4 makes this better. OK cheers, I have got plans to migrate over the next month or two; just wanted to point out the issue. Andy -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Compile errors when building 32-bit on 64-bit system
Andy Shellam writes: > I had to install PG 8.3.7 on another couple of machines today, and got > exactly the same issue (setting all 3 variables) again on the SPI > module. Please see as follows: FWIW, it does appear that 8.4 makes this better. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Compile errors when building 32-bit on 64-bit system
That sounds like you hadn't yet figured out to set CFLAGS too. Google or no Google, you're setting the wrong variable. I had to install PG 8.3.7 on another couple of machines today, and got exactly the same issue (setting all 3 variables) again on the SPI module. Please see as follows: Configure line: CFLAGS="-m32" LD="ld -melf_i386" LDFLAGS="-m32" ./configure etc "make" fails with the error: gcc -shared -o refint.so refint.o /usr/bin/ld: warning: i386 architecture of input file `refint.o' is incompatible with i386:x86-64 output refint.o: In function `check_primary_key': refint.c:(.text+0x8f): undefined reference to `elog_start' refint.c:(.text+0xa5): undefined reference to `elog_finish' ... etc ... Running the following commands manually allows the install to continue - notice how I have to add "-m32" to the second gcc command: # cd contrib/spi # gcc -m32 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fpic -DREFINT_VERBOSE -I. -I../../src/include -D_GNU_SOURCE -I/opt/nmail32/libxml2/include/libxml2 -I/opt/nmail32/libxml2/include/libxml2 -I/opt/nmail32/zlib/include -I/opt/nmail32/readline/include -I/opt/nmail32/openssl/include -I/opt/nmail32/ncurses/include -c -o refint.o refint.c # gcc -m32 -shared -o refint.so refint.o And the same for "autoinc.o": # gcc -m32 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fpic -DREFINT_VERBOSE -I. -I../../src/include -D_GNU_SOURCE -I/opt/nmail32/libxml2/include/libxml2 -I/opt/nmail32/libxml2/include/libxml2 -I/opt/nmail32/zlib/include -I/opt/nmail32/readline/include -I/opt/nmail32/openssl/include -I/opt/nmail32/ncurses/include -c -o autoinc.o autoinc.c # gcc -m32 -shared -o autoinc.so autoinc.o # make All of PostgreSQL successfully made. Ready to install. So it appears the SPI module is respecting CFLAGS, but ignoring LDFLAGS. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Questions on setup and usage
I know that by using lowercase object names that I can work with those object in a case insensitive way, however is there a way through maybe a locale or character set to make the data and the indexes case insensitive such that you don't need to use ILIKE? Best Regards Michael Gould "Kevin Grittner" wrote: > Michael Gould wrote: > >> 1. The ability to encrypt stored procedures and triggers. I know >> that PostGres doesn't have this feature but is there a way to >> protect stored procedure and trigger source yet allow it to be >> executed by any user and how would it be done. > > You could code them in a compiled language (like C) and the user would > only see a reference to the library file. > >> 2. We do not want the table schema to be accessed by outside >> applications. Since our application is so normalized, accessing >> these tables directly will not make a lot of sense to our users so >> we create views that are more user friendly for doing reports, >> accessing the data via Excel, etc. How would we do this in >> Postgres. > > You could put the tables in a separate schema, which you would not put > on the default search path. The views could refer to the tables with > the schema qualifier. This would not hide the tables from the users, > per se, but would cause them not to show on casual display, or allow > them to be accessed without explicitly specifying the schema. > >> 3. We use active directory to validate a user's login. We have >> "rules" and functionality security built in as stored procedures. >> How would we do this under Postgres. We do this with a >> configuration variable during the login process stored process. If >> the company uses active directory we use it to validate the user, if >> not then we present a user login and require that a login be >> manually entered. For those companies that use active directory we >> try to have our application validate the AD so that the user isn't >> required to multiple applications. > > I don't know anything about that. Perhaps someone else will jump in > with advice. > > -Kevin > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Questions on setup and usage
Please keep the list copied. Also, it's best to quote the minimum required for context, and to avoid top-posting. (It's much easier for most people to read this way.) Michael Gould wrote: > "Kevin Grittner" wrote: >>> 3. We use active directory to validate a user's login. We have >>> "rules" and functionality security built in as stored procedures. >>> How would we do this under Postgres. We do this with a >>> configuration variable during the login process stored process. >>> If the company uses active directory we use it to validate the >>> user, if not then we present a user login and require that a login >>> be manually entered. For those companies that use active >>> directory we try to have our application validate the AD so that >>> the user isn't required to multiple applications. >> >> I don't know anything about that. Perhaps someone else will jump >> in with advice. > Thanks, I think I can use Kerbos 5 in order to handle the security > login. > I also find that it would be nice that instead of putting a date to > have the password expire on a user that you could set the number of > days that a password expires in. I guess we could write a user > function for that. > Is there any way using a character set or locale setting to make > sure that the where clause is case insensitive other than using > ILIke? I believe so, but I'll leave the details to those more familiar with the issue. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Questions on setup and usage
Michael Gould wrote: > 1. The ability to encrypt stored procedures and triggers. I know > that PostGres doesn't have this feature but is there a way to > protect stored procedure and trigger source yet allow it to be > executed by any user and how would it be done. You could code them in a compiled language (like C) and the user would only see a reference to the library file. > 2. We do not want the table schema to be accessed by outside > applications. Since our application is so normalized, accessing > these tables directly will not make a lot of sense to our users so > we create views that are more user friendly for doing reports, > accessing the data via Excel, etc. How would we do this in > Postgres. You could put the tables in a separate schema, which you would not put on the default search path. The views could refer to the tables with the schema qualifier. This would not hide the tables from the users, per se, but would cause them not to show on casual display, or allow them to be accessed without explicitly specifying the schema. > 3. We use active directory to validate a user's login. We have > "rules" and functionality security built in as stored procedures. > How would we do this under Postgres. We do this with a > configuration variable during the login process stored process. If > the company uses active directory we use it to validate the user, if > not then we present a user login and require that a login be > manually entered. For those companies that use active directory we > try to have our application validate the AD so that the user isn't > required to multiple applications. I don't know anything about that. Perhaps someone else will jump in with advice. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] postgresql won't restart. Cannot allocate memory. Must reboot. Any alternative short of reboot?
Hi, I am a very happy user of Postgresql! Thank you all for this marvelous work! I have an older linux server running debian etch 4.0 using the old postgresql-7.4. There is essentially a single application running on that machine which serves up data from a single postgresql database. There is no new data added to the database. It is simply serving up legacy information. The server does nothing else. I suspect I am working at the limits of the memory capabilities of this machine. It has 1G of ram. My postgresql database has a few very small control tables and has a single very large table LTA_IDB (the pg_dump of this main table is 1.9G in size ) and du -sh /var/lib/postgresql/7.4/ 5.7G/var/lib/postgresql/7.4/ These are my settings in postgresql.conf: shared_buffers = 48000 max_connections = 512 sort_mem = 4096 effective_cache_size = 4000 wal_buffers = 8 -- while in the file /etc/sysctl.conf kernel.shmmax = 63500 -- The server works fine most of the time, though occasionally has trouble and my application fails When I log into the machine I see that Postgresql has closed and the database wont restart. Thus when I log in to the machine and try to restart postgresql I get this message: A2006:/home/wustl# /etc/init.d/postgresql-7.4 start Starting PostgreSQL 7.4 database server: main* The PostgreSQL server failed to start. Please check the log output: 2009-07-06 09:37:56 [1251] FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5432001, size=407429120, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 407429120 bytes). The PostgreSQL documentation contains more information about shared memory configuration. failed! Thus interactive restarting the database seems not to work at that point. However I can easily restart the postgresql database system by rebooting the server itself. Then postgresql will start easily and all is fine. Question 1: Is there something I can do besides rebooting the whole server to reset the memory so that postgreql will start up again? What does rebooting do? Question 2: I would prefer to do the minimal changes to preserve the working of this system, and avoid this recurrent problem. Might changing the paramaters help? I would prefer not to have to add memory to this older system, as I might have trouble matching the memory and the server is far from where I am etc. If I do need to add memory, what should I set the parameters to? Question 3: I run the vacuum program 4 times a day from a cron job. 30 1 * * * vacuumdb --analyze LTA_IDB 30 12 * * * vacuumdb --analyze LTA_IDB 30 17 * * * vacuumdb --analyze LTA_IDB 30 20 * * * vacuumdb --analyze LTA_IDB where LTA_IDB is the large database table mentioned above. Is this neccessary for a system that is no longer is storing new information? What does it accomplish? Does it tune it more for the queries? Thank you so much! Mitchell -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Make the primary key a multilingual value
On Saturday 04 July 2009 20:44:11 Ransika de Silva wrote: > I have a requirement of storing multilingual words and hence planing to > utilize the Unicode support of the database tables. The question that I > have is, will there be a problem if I make the primary key of a table > multilingual, Not unless you create the problem yourself. :-) Shouldn't be a problem as far as PostgreSQL is concerned. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] order by question
> I agree with Scott, if your application generate IN condition, could create > CASE too, looks like this > SELECT id, product_id FROM your_table > WHERE product_id IN (6, 3, 4, 10, 7) > ORDER BY > CASE > WHEN product_id = 6 THEN 1 > WHEN product_id = 3 THEN 2 > WHEN product_id = 4 THEN 3 > WHEN product_id = 10 THEN 4 > WHEN product_id = 7 THEN 5 > END; A shorter solution would be ORDER BY product_id != 6, product_id != 3, product_id != 4, product_id != 10, product_id != 7, product_id -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
