Re: [GENERAL] [ADMIN] Fast Deletion For Large Tables
On 5 Oct 2002 at 20:17, Nikolaus Dilger wrote: > Partitioned tables would solve your issue since you > could just truncate a partiotion in order to delete the > unneeded data. Unfortunately they are not available in > PostgreSQL. But maybe in a future release. If you don't mind realigning your schema, inheriting tables can closely mimick partitions upto certain extent. Reading the original post below, I recommend you to consider inheritance approach. Create a base table which is empty and create inherited partitions. You can get all the data in child table by querying upon base table. So your application would not require any modification as long as selecting data is considered. You have to write a wrapper on insert/update/delete code to operate upon a particular partition. > There is no rowid in PostgreSQL. So while adding an > additional column with a sequence would help as long as > you have an index. Just indexing your date column > maybe easier. But then you pay with slower inserts or > the time and effort to build the index. There is oid and if you don't use it you can disable it saving some space if you have large data. HTH Bye Shridhar -- Bachelor: A man who chases women and never Mrs. one. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Debug information
Hallo, I found in reference.pdf small part about debug level. Where can I read more about the 17 debug levels? Best Regards, Rafal At 18:06 07.10.2002 -0500, Devinder K Rajput wrote: >You can display a lot of debug info setting up loggin. Make the following >changes in your postgresql.conf file for some basic logging. The higher up >you go with the debug level, the more info you'll get. > > debug_level = 2 # range 0-16 > debug_print_query = true > debug_pretty_print = true > >regards, > >Devinder Rajput >Stores Division Corporate Offices >Chicago, IL >(773) 442-6474 > > > > > "Rafal > Kedziorski" > > <[EMAIL PROTECTED]> To: > [EMAIL PROTECTED] > Sent > by: cc: > > pgsql-admin-owner@post Subject: [ADMIN] > Debug information > gresql.org > > > > > > 10/07/2002 06:01 > PM > > > > > > > > > >Hallo, > >I'm working on porting Oracle schema to PostgreSQL. Now I habe the whole >schema in PostgreSQL. But there are some problems in our application in >some sql queries. I get following errors. > >ERROR: Unable to identify an operator '<=' for types 'numeric' and 'double >precision' > You will have to retype this query using an explicit cast > >Its possible to display the queries in debug print out ftom PostgreSQL with >queries which failed? If yep, how? > > >Best Regards, >Rafal > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Two questions.
Hello ! Where I can find information, to know since one is organized the system of files of a PostgreSQL database ? ( files of database objects ). If I have a " disc crash " or lose accidental one or several files, since I can realize which lack? Thank you very much. Fabio Bon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] pg_dump command inside shell scripts
Hi Folks!
Again I need a help from you!
I have develop a shell script (seen below) to do dump and vaccum
automatically.
Once pg_dump command prompts for password, I changed the $PGDATA/*hba.conf
file
to identify Linux user which starts the shell script to avoid password
prompt.
But now I need change the $PGDATA/*hba.conf file as it was originally
and
face the password prompt again.
In shell script programming how do I can do to answer this prompt?
I have done something like this:
#> pd_dump -d zakal < **" > ${LOG}
PARAM="-U ${USER} -Fp -v -s -c -X use-set-session-authorization"
${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1
if [ $? -ne 0 ]; then
ERR_VAL=$?
echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
echo "*** Comando: ${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1"
>> ${LOG}
cat ${LOG} | \
mail -s "`hostname`: Erro no deump DLL do banco ${PGDB}"
[EMAIL PROTECTED]
fi
ARQ=${BKHOME}/${PGDB}.data
echo "** ascii do banco <${PGDB}> **" >> ${LOG}
PARAM="-U ${USER} -Fp -a -v -X use-set-session-authorization"
${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1
if [ $? -ne 0 ]; then
ERR_VAL=$?
echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
echo "*** Comando: ${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1"
>> ${LOG}
cat ${LOG} |\
mail -s "`hostname`: Erro no dump ASCII do banco ${PGDB}"
[EMAIL PROTECTED]
fi
ARQ=${BKHOME}/${PGDB}.tar
echo "** tar do banco <${PGDB}> **" >> ${LOG}
PARAM="-U ${USER} -Ft -v -X use-set-session-authorization"
${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1
if [ $? -ne 0 ]; then
ERR_VAL=$?
echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
echo "*** Comando: ${DUMPCMD} ${PARAM} ${PGDB} -f ${ARQ} >> ${LOG} 2>&1"
>> ${LOG}
cat ${LOG} |\
mail -s "`hostname`: Erro no dump tar do banco ${PGDB}"
[EMAIL PROTECTED]
fi
###
# Parametro de vaccum
###
# -d: dbname
# -z: Calculate statistics for use by the optimizer.
# -f: Perform "full" vacuuming
# -v: verbose
###
echo "** vacuum de <${PGDB}> **" >> ${LOG}
PARAM="-U ${USER} -z -v -f"
${VACUUMCMD} ${PARAM} -d ${PGDB} > ${LOG} 2>&1
if [ $? -ne 0 ]; then
ERR_VAL=$?
echo "*** Erro no: ${ERR_VAL}" >> ${LOG}
echo "ERRO: vacuumdb ${VACUUMCMD} ${PARAM} -d ${PGDB} > ${LOG} 2>&1" >>
${LOG}
cat ${LOG} |\
mail -s "`hostname`: Erro no vacuum do banco ${PGDB}"
[EMAIL PROTECTED]
fi
..
A Question...
Since before your sun burned hot in space
and before your race was born,
I have awaited a question.
Elielson Fontanezi
DBA Technical Support - PRODAM
Parque do Ibirapuera s/n - SP - BRAZIL
+55 11 5080 9493
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] pg_dump command inside shell scripts
On Tue, 8 Oct 2002, Elielson Fontanezi wrote: > to identify Linux user which starts the shell script to avoid password > prompt. There's a virtually-undocmented environment variable: PGPASSWORD PGPASSWORD="secret-password" export PGPASSWORD # Now pg_dump will not prompt for password. pg_dump -U postgres ... Make sure you only use this in a script which normal users cannot read, and on a system which hides root-process environment variables from being displayed by normal users (with ps). -- David. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] problems with pltcl.so
"Josh Goldberg" <[EMAIL PROTECTED]> writes: > ERROR: Load of file /home/postgres/lib/pltcl.so failed: dlopen '/home/post= > gres/lib/pltcl.so' failed. (Shared object "libtcl80.so.1" not found) > I am on freebsd 4.4, I have libtcl80.so.1 in /usr/local/lib and not only is= > it in my ld-elf.so.hints file but I also manually put /usr/local/lib in my= > LD_LIBRARY_PATH to no avail. I'm not very familiar with freebsd, but if it uses LD_LIBRARY_PATH, keep in mind that the value that counts is the one in the postmaster's environment. Did you restart the postmaster with the corrected path in its environment variables? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Debug information
POLONIUM-Team wrote: > Hallo, > > I found in reference.pdf small part about debug level. Where can I read > more about the 17 debug levels? The levels are really only 1-5, and they just represent increasing amounts of detail. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Two questions.
Hello ! Where I can find information, to know since one is organized the system of files of a PostgreSQL database ? ( files of database objects ). If I have a " disc crash " or lose accidental one or several files, since I can realize which lack? Thank you very much. Fabio Bon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] phpPgAdmin + PostgreSQL + authentication
Title: phpPgAdmin + PostgreSQL + authentication Howdy: Not sure if this the most likely of maillists to ask, but is anyone using phpPgAdmin? I have a few questions regarding authentication of username / passwords. I'm running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7-10. I have phpPgAdmin 2.4.2 installed. Basically, I want to know: how to configure phpPgAdmin to allow all the users that exist in pg_shadow to log in and be authenticated? In my pg_hba.conf, I have this: [snip conf file] # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE AUTH_ARGUMENT local all trust host all 127.0.0.1 255.255.255.255 md5 host all 16.x.x.1 255.0.0.0 md5 host all 192.168.0.0 255.255.255.0 md5 [/snip conf file] In the config.inc.php, I have this: [snip php conf] // The $cfgServers array starts with $cfgServers[1]. Do not use $cfgServers[0]. // You can disable a server config entry by setting host to ''. $cfgServers[1]['local'] = false; $cfgServers[1]['host'] = 'test.localserver.net'; $cfgServers[1]['port'] = '5432'; $cfgServers[1]['adv_auth'] = true; $cfgServers[1]['user'] = ''; // if you are not using adv_auth, // enter the username to connect all the time $cfgServers[1]['password'] = ''; // if you are not using adv_auth and // a password is required enter a password $cfgServers[1]['only_db'] = ''; // if set to a db-name, only this db is accessible [/snip php conf] As I understand it, shouldn't this allow any user with TCP connection to access the database? I suppose I am trying to understand if adv_auth even uses pg_shadow at all, or, does 'local' means that no authentication is needed, anyone can log in. The only thing that happens at the index.php page is when I log in, I get "Wrong username/password. Access denied". I mean, if I can access the database via command line (psql -U joe -d testdb) without needing to authenticate myself, shouldn't that mean that phpPgAdmin allows the same thing? Otherwise, I should be able to use what's in pg_shadow, right? I am re-reading the documentation page. Any info / direction is appreciated. Thanks! -X
[ADMIN] Two questions.
Sorry about my previous message. It wasn't clear. Where can I find information about how the system of files of a PostgreSQL database is organized ? ( files of database objects ). If I have a " disc crash " or I lose accidentally one or several files, how can I realize which are lacking ? Thank you very much. Fabio Bon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] problems with pltcl.so
my goodness that didn't even brush past my mind! thanks much for the assist! :) - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Josh Goldberg" <[EMAIL PROTECTED]> Cc: "postgres-admin" <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 6:50 AM Subject: Re: [ADMIN] problems with pltcl.so > "Josh Goldberg" <[EMAIL PROTECTED]> writes: > > ERROR: Load of file /home/postgres/lib/pltcl.so failed: dlopen '/home/post= > > gres/lib/pltcl.so' failed. (Shared object "libtcl80.so.1" not found) > > > I am on freebsd 4.4, I have libtcl80.so.1 in /usr/local/lib and not only is= > > it in my ld-elf.so.hints file but I also manually put /usr/local/lib in my= > > LD_LIBRARY_PATH to no avail. > > I'm not very familiar with freebsd, but if it uses LD_LIBRARY_PATH, keep > in mind that the value that counts is the one in the postmaster's > environment. Did you restart the postmaster with the corrected path in > its environment variables? > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] problems with pltcl.so
It was listed in the ldconfig hints file, but adding LD_LIBRARY_PATH to the postmaster's login environment explicitly did the trick. - Original Message - From: "Chad R. Larson" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]>; "Josh Goldberg" <[EMAIL PROTECTED]> Cc: "postgres-admin" <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 11:34 AM Subject: Re: [ADMIN] problems with pltcl.so > In FreeBSD there is a utility called "ldconfig(8)" that sets up the hint > cache. If you add something to a library directory you will need to re-run > it, or reboot (it is run out of /etc/rc). > > At 06:50 AM 10/8/02 , Tom Lane wrote: > >"Josh Goldberg" <[EMAIL PROTECTED]> writes: > > > ERROR: Load of file /home/postgres/lib/pltcl.so failed: dlopen > > '/home/post= > > > gres/lib/pltcl.so' failed. (Shared object "libtcl80.so.1" not found) > > > > > I am on freebsd 4.4, I have libtcl80.so.1 in /usr/local/lib and not > > only is= > > > it in my ld-elf.so.hints file but I also manually put /usr/local/lib > > in my= > > > LD_LIBRARY_PATH to no avail. > > > >I'm not very familiar with freebsd, but if it uses LD_LIBRARY_PATH, keep > >in mind that the value that counts is the one in the postmaster's > >environment. Did you restart the postmaster with the corrected path in > >its environment variables? > > > > regards, tom lane > > > >---(end of broadcast)--- > >TIP 4: Don't 'kill -9' the postmaster > > -crl > -- > Chad R. Larson (CRL22)[EMAIL PROTECTED] >Eldorado Computing, Inc. 602-604-3100 > 5353 North 16th Street, Suite 400 > Phoenix, Arizona 85016-3228 > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] PostgreSQL and 2-node failover cluster solutions
On Sun, Oct 06, 2002 at 03:21:14PM +0200, Ragnar Kjørstad wrote: > Well; if you have a single NetApp then you still have a single point of > failure (avoiding that is the whole purpose of failover, right?), and if > you have two of them then it's one pretty damn expensive > postgresql-server :) Indeed we run our Netapps as a clustered pair. If you already have the facilities available or can get them for a good price second hand, then I am happy to let the group know that running PostgreSQL servers on Linux and Netapps is not only possible, it is a fast, reliable and stable platform. > Anyway. > > I presume you are using it to failover PostgreSQL servers? > > No, currently we're only using it for failover lvs- and file-servers. > The principle is the same though: Heartbeat runs on two servers > connected with multiple "heartbeat-channels" (ethernet, serial). > They constantly monitor each other, and if the secondary server looses > contact with the primary it will start the "services" locally. In your > case the services would be an IP-address and the postgresql-server. > > You also need some "fencing" to make sure that the two servers don't > both start postgresql by accident. This is done with "stonith" (Shoot > the other node in the head) - before the secondary server starts > postgresql it will cut the power to the primary server to make sure it's > not up. > > That's pretty much it. Great, sounds very similar to Kimberlite, and also to our requirements. I will give it a test. > Be awere that failover doesn't solve any problem in the world though: If > postgresql corrupts it's files, both servers are screwed. Also, failover > will complicate the setup, and in general a more complicated setup means > more downtime (operator-error) - so, make sure you read the > documentation and understand how it works (or pay someone to do it for > you). Naturally, that's why we are after as simple a solution as possible. That is also why we went for a simple, free and open database like PostgreSQL rather than a more complex system like Oracle (of course, cost was a factor also :). Thanks for the cluster info. If anyone wants to discuss HA PostgreSQL setups more, drop me a line. Cheers, Chris. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Two questions.
[EMAIL PROTECTED] wrote: > Hello ! > > Where I can find information, to know since one is organized the system of > files of a PostgreSQL database ? ( files of database objects ). > > If I have a " disc crash " or lose accidental one or several files, since I > can realize which lack? I wrote something up for 7.3: http://developer.postgresql.org/docs/postgres/diskusage.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
