[ADMIN] Show tables query
Hi, Is there an SQL command supported by Postgres to return a list of tables in a database? For example on mySQL, you can connect to a database and issue the command SHOW TABLES to bring back a list of tables in that database. In PG this throws the error "unknown configuration parameter TABLES." Any ideas? Thanks Andy
Re: [ADMIN] Show tables query
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Andy Shellam wrote: | Hi, | | Is there an SQL command supported by Postgres to return a list of | tables in a database? | | For example on mySQL, you can connect to a database and issue the | command SHOW TABLES to bring back a list of tables in that database. | In PG this throws the error unknown configuration parameter TABLES. Hello, Andy. Not a command per se, but there are two ways you can obtain this information, depending on where you're working. The first option is the backslash commands you can use from f.e. psql (type \? in a psql prompt to see the full list), where \d will list all sorts of database objects, \dt can be used specifically for tables. The other option which you can use from an SQL script is accessing the system tables pg_class, pg_namespace and pg_tablespace in schema pg_catalog, using a query similar to those used by the backslash commands: ~ template1=# SELECT c.relname AS table FROM pg_class c ~ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ~ WHERE n.nspname = 'public' ~ AND c.relkind = 'r'; Substitute 'public' for whatever schema you're interested in or add other schemas according to your preference. Also, take a look at system view pg_tables (\d pg_tables). Also, take a look at the archives, Elein once posted a nice set of views ~ and statements you can use for such purposes. Hope this helped, - -- ~Grega Bremec ~gregab at p0f dot net -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFEL5qjfu4IwuB3+XoRA7CDAKCBFI7749wtFfsf4GXm3JcPQBU81gCffZMT GbeTZzo0T3RJBwvwlK61O9c= =NhhS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Show tables query
On Sun, 2 Apr 2006, Grega Bremec wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Andy Shellam wrote: | Hi, | | Is there an SQL command supported by Postgres to return a list of | tables in a database? | | For example on mySQL, you can connect to a database and issue the | command SHOW TABLES to bring back a list of tables in that database. | In PG this throws the error unknown configuration parameter TABLES. Hello, Andy. Not a command per se, but there are two ways you can obtain this information, depending on where you're working. The first option is the backslash commands you can use from f.e. psql (type \? in a psql prompt to see the full list), where \d will list all sorts of database objects, \dt can be used specifically for tables. The other option which you can use from an SQL script is accessing the system tables pg_class, pg_namespace and pg_tablespace in schema pg_catalog, using a query similar to those used by the backslash commands: ~ template1=# SELECT c.relname AS table FROM pg_class c ~ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ~ WHERE n.nspname = 'public' ~ AND c.relkind = 'r'; Substitute 'public' for whatever schema you're interested in or add other schemas according to your preference. Also, take a look at system view pg_tables (\d pg_tables). Also, take a look at the archives, Elein once posted a nice set of views ~ and statements you can use for such purposes. psql -E will show you all queries that internal commands generate Hope this helped, - -- ~Grega Bremec ~gregab at p0f dot net -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFEL5qjfu4IwuB3+XoRA7CDAKCBFI7749wtFfsf4GXm3JcPQBU81gCffZMT GbeTZzo0T3RJBwvwlK61O9c= =NhhS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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
FW: [ADMIN] Show tables query
Hi Grega, That's perfect thanks. I needed the SQL as it will be running in a web application, and I need to use the resulting list for processing within the application. Many thanks Andy -Original Message- From: Grega Bremec [mailto:[EMAIL PROTECTED] Sent: Sunday, 02 April, 2006 10:34 am To: [EMAIL PROTECTED] Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Show tables query -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Andy Shellam wrote: | Hi, | | Is there an SQL command supported by Postgres to return a list of | tables in a database? | | For example on mySQL, you can connect to a database and issue the | command SHOW TABLES to bring back a list of tables in that database. | In PG this throws the error unknown configuration parameter TABLES. Hello, Andy. Not a command per se, but there are two ways you can obtain this information, depending on where you're working. The first option is the backslash commands you can use from f.e. psql (type \? in a psql prompt to see the full list), where \d will list all sorts of database objects, \dt can be used specifically for tables. The other option which you can use from an SQL script is accessing the system tables pg_class, pg_namespace and pg_tablespace in schema pg_catalog, using a query similar to those used by the backslash commands: ~ template1=# SELECT c.relname AS table FROM pg_class c ~ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ~ WHERE n.nspname = 'public' ~ AND c.relkind = 'r'; Substitute 'public' for whatever schema you're interested in or add other schemas according to your preference. Also, take a look at system view pg_tables (\d pg_tables). Also, take a look at the archives, Elein once posted a nice set of views ~ and statements you can use for such purposes. Hope this helped, - -- ~Grega Bremec ~gregab at p0f dot net -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) iD8DBQFEL5qjfu4IwuB3+XoRA7CDAKCBFI7749wtFfsf4GXm3JcPQBU81gCffZMT GbeTZzo0T3RJBwvwlK61O9c= =NhhS -END PGP SIGNATURE- !DSPAM:14,442f9a9c35047994616568! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Show tables query
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Andy Shellam) belched out: Is there an SQL command supported by Postgres to return a list of tables in a database? Yes, it's called SELECT. There is a standard schema called INFORMATION_SCHEMA, which contains a variety of relevant views. Notably, you could request: SELECT * FROM INFORMATION_SCHEMA.TABLES; That has the merit of actually conforming to SQL standards... -- output = reverse(moc.liamg @ enworbbc) http://cbbrowne.com/info/ They have finally found the most ultimately useless thing on the web... Found at the Victoria's Secret website: The online shop: Text Only Listing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Show tables query
On 4/2/06, Christopher Browne [EMAIL PROTECTED] wrote: After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Andy Shellam) belched out: Is there an SQL command supported by Postgres to return a list of tables in a database? Yes, it's called SELECT.There is a standard schema called INFORMATION_SCHEMA, which contains avariety of relevant views.Notably, you could request: SELECT * FROM INFORMATION_SCHEMA.TABLES where table_type='BASE TABLE' ; if you need tables only otherwise it returns the Views also. That has the merit of actually conforming to SQL standards...--output = reverse( moc.liamg @ enworbbc)http://cbbrowne.com/info/They have finally found the most ultimately useless thing on the web...Found at the Victoria's Secret website: The online shop: Text Only Listing---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: FW: [ADMIN] Setting up of PITR system.
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Rajesh Kumar Mallah wrote:
|
| Instead of taking a round about method i am using the content of the
| file, (This was also suggested by Andy at somepoint)
After reading the docs again, that's what I would do as well, indeed. :)
| lately i feel that we should not be discussing the topic over here
| becoz it has less to do with postgresql and more of bash.
I've been considering that seriously in the very first post I wrote, but
since there seems to be a lot of people interested in a working,
flexible WAL archiving script, I decided to keep it on the list. It is
an administration issue, afterall. I will stand corrected if someone
feels we're clogging their mailboxes.
| ##
| #START WAL LOCATION: E/A9145E4 (file 0001000E000A)
| #CHECKPOINT LOCATION: E/A92939C
| #START TIME: 2006-04-01 14:36:48 IST
| #LABEL: base_backup_01-04-2006-14-36-45
| ###
|
| BACKUP_LABEL=$DATADIR/backup_label
| # get the like containing line START WAL LOCATION
|
| START_LINE=`grep -i START WAL LOCATION $BACKUP_LABEL`
| # strip something like 'START WAL LOCATION: E/A9145E4 (file ' from begin.
| START_LINE=${START_LINE/#START*file /}
| # strip ')' from end.
| START_LINE=${START_LINE/%)/}
| # REF_FILE_NUM is something like 0001000A0068
| REF_FILE_NUM=$START_LINE
Why not go for the entire filename? Record offset is never going to be
more than eight characters, as include/access/xlogdefs.h states:
~ typedef struct XLogRecPtr
~ {
~ uint32xlogid; /* log file #, 0 based */
~ uint32xrecoff; /* byte offset of location in log file */
~ } XLogRecPtr;
A 32 bit unsigned integer can always be represented in eight hexadecimal
digits or less.
~ REF_FILE=`grep 'START WAL LOCATION' ${BACKUP_LABEL} | \
~awk '{
~sub(/)/, , $6);
~sub(/[0-9A-F]\//, , $4);
~printf(%s.%08s.backup, $6, $4);
~ }'`
This will remove the trailing paren from WAL filename (field 6), the
leading xlogid and the slash from WAL location (field 4) and compose
them into the full filename, zero-padding WAL location to eight
characters and giving back something like this:
~0001000E000A.0A9145E4.backup
What you need to do now is just appendd a glob (if your archive_method
consists of gzip/bzip2/...) and prepend ${WAL_ARCHIVE}:
~ REF_FILE=${WAL_ARCHIVE}/${REF_FILE}*
| ~ RM_LIST=
| ~ find ${WAL_ARCHIVE} -type f | sort -g | while read archive; do
| ~if [ ! ${archive} = ${REF_FILE} ]; then
| ~ RM_LIST=${RM_LIST:+${RM_LIST} }${archive}
| ~else
| ~ break
| ~fi
| ~ done
| ~ rm -f ${RM_LIST}
| i think you meant instead of '=' in above [comparison].
Absolutely not. :) What we're doing here is we're looking at all files
in ${WAL_ARCHIVE} (find), sorting them according to their general
numeric value (sort, lowest first) and adding them one-by-one to the
list of WALs to remove (RM_LIST assignment) until we find REF_FILE (the
equals not comparison). As soon as we find REF_FILE, we escape the
while loop (break) and remove all the old log files (rm -f).
Since WALs are numbered in a sequence, and location identifiers in a WAL
which are also a part of the filename are sequential too, sorting will
always produce a list of WAL segments in chronological order, oldest
first, newest last.
What is critical to the above piece of code is that BOTH ${archive} and
${REF_FILE} are either absolute filenames or relative ones, of course,
otherwise they'll never match.
| regarding
| $ env LC_ALL=C backup_script.sh
If you do it inside the script, you shoud definitely export it to
subshells since all the backtick commands execute in a subshell. Using
sort -g to sort the listing according to general numeric value is the
safest option though, and it is also the least disruptive one as it
doesn't require any changes to the environment.
Kind regards,
- --
~Grega Bremec
~gregab at p0f dot net
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
iD8DBQFEL9x/fu4IwuB3+XoRA3IgAJ9Qn7dYsNhv3e9f+P64mJoiz+s77gCeLELY
4xAxFb3Ncd8RHWkBbgyag7U=
=7MXQ
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [ADMIN] Show tables query
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Andy Shellam) belched out: Is there an SQL command supported by Postgres to return a list of tables in a database? Sorry, did I say something wrong? I thought it was a perfectly valid question actually. The application in mind is going to be run exclusively on Postgres, so I'm not overly fussed over standards - I just wanted a quick win, of which Grega's SQL gave it me perfectly - tables only, nothing else included. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Show tables query
On 4/2/06, Andy Shellam [EMAIL PROTECTED] wrote: After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Andy Shellam) belched out: Is there an SQL command supported by Postgres to return a list of tables in a database? Sorry, did I say something wrong? I thought it was a perfectly valid question actually. The application in mind is going to be run exclusively on Postgres, so I'm not overly fussed over standards - I just wanted a quick win, of which Grega's SQL gave it me perfectly - tables only, nothing else included. the information_schema approach is still better than querying the system catalogs. The system catalogs are internal to postgresql what if future versions of postgresql change the sys catalogs dramatically ? (your app breaks!) information_schema is the standard which are more likely to behave the same in all versions of pgsql becoz they are(currently) views on the sys catalogs. Regds Rajesh Kumar Mallah. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Show tables query
Thanks Rajesh, That's always a risk anyway with anything - hence where upgrade testing comes in ;-) I'll probably go this way if I do indeed have this need still - it was only a preliminary thought process, I just thought I'd ask the question. Thanks Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Kumar Mallah Sent: Sunday, 02 April, 2006 4:32 pm To: [EMAIL PROTECTED] Cc: Christopher Browne; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Show tables query On 4/2/06, Andy Shellam [EMAIL PROTECTED] wrote: After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Andy Shellam) belched out: Is there an SQL command supported by Postgres to return a list of tables in a database? Sorry, did I say something wrong? I thought it was a perfectly valid question actually. The application in mind is going to be run exclusively on Postgres, so I'm not overly fussed over standards - I just wanted a quick win, of which Grega's SQL gave it me perfectly - tables only, nothing else included. the information_schema approach is still better than querying the system catalogs. The system catalogs are internal to postgresql what if future versions of postgresql change the sys catalogs dramatically ? (your app breaks!) information_schema is the standard which are more likely to behave the same in all versions of pgsql becoz they are(currently) views on the sys catalogs. Regds Rajesh Kumar Mallah. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend !DSPAM:14,442feeb335041315618668! ---(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: FW: [ADMIN] Setting up of PITR system.
On 4/2/06, Grega Bremec [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Rajesh Kumar Mallah wrote:
|
| Instead of taking a round about method i am using the content of the
| file, (This was also suggested by Andy at somepoint)
After reading the docs again, that's what I would do as well, indeed. :)
| lately i feel that we should not be discussing the topic over here
| becoz it has less to do with postgresql and more of bash.
I've been considering that seriously in the very first post I wrote, but
since there seems to be a lot of people interested in a working,
flexible WAL archiving script, I decided to keep it on the list. It is
an administration issue, afterall. I will stand corrected if someone
feels we're clogging their mailboxes.
| ##
| #START WAL LOCATION: E/A9145E4 (file 0001000E000A)
| #CHECKPOINT LOCATION: E/A92939C
| #START TIME: 2006-04-01 14:36:48 IST
| #LABEL: base_backup_01-04-2006-14-36-45
| ###
|
| BACKUP_LABEL=$DATADIR/backup_label
| # get the like containing line START WAL LOCATION
|
| START_LINE=`grep -i START WAL LOCATION $BACKUP_LABEL`
| # strip something like 'START WAL LOCATION: E/A9145E4 (file ' from begin.
| START_LINE=${START_LINE/#START*file /}
| # strip ')' from end.
| START_LINE=${START_LINE/%)/}
| # REF_FILE_NUM is something like 0001000A0068
| REF_FILE_NUM=$START_LINE
Why not go for the entire filename?
it takes a while (i dont know how much) for the .backup file to get
archived and appear in the wal archive area. thats why i prefer to
use the wal log filename (0001000A0068) instead of
something like 0001000A0068.0A348A45.backup.
Do you see any problem in the current approach ?
i have seen it working fine till now.
Another area i was thinking to improve this script was to
make it dig out all the tablespace folders to be archived by looking
into PGDATADIR/pg_tblspc . This shall make the script more generic.
pg_tblspc as contents like below:
$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 19 Mar 27 21:45 16391 - /mnt/indexspace_new
lrwxrwxrwx 1 postgres postgres 18 Mar 27 21:45 16392 - /mnt/bigtables_new
can you suggest the sane/recommended way to get the destination
folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf %??? )
or do i parse output of ls !
if above is done i see the script INPUT/OUTPUT'ACTIVITY as below
INPUTS: PGDATADIR , WAL_ARCHIVE_DIR , LOCAL DUMP DIRECTORY
OUTPUT: n/a
ACTIVITY:
1. it shall take a base backup of PGDATADIR (minus pg_xlog) and
all tablespaces into *LOCAL* DUMP Directory
2. Frees disk space by removing unwanted LOG files in WAL_ARCHIVE_DIR
i am waiting for my another machine to get fitted with 2 more drives
so that i can test/develop scripts for the restoration part and modify
the script for remote base backups and archiving. that shall
happen in a week or so.
thanks for your support till now.
Regds
Rajesh Kumar Mallah.
Kind regards,
- --
~Grega Bremec
~gregab at p0f dot net
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
iD8DBQFEL9x/fu4IwuB3+XoRA3IgAJ9Qn7dYsNhv3e9f+P64mJoiz+s77gCeLELY
4xAxFb3Ncd8RHWkBbgyag7U=
=7MXQ
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Show tables query
Rajesh Kumar Mallah [EMAIL PROTECTED] writes: information_schema is the standard which are more likely to behave the same in all versions of pgsql becoz they are(currently) views on the sys catalogs. Unfortunately, the SQL committee did tremendous damage to that argument by changing the definitions of some of those views in SQL2003 :-( I'd still agree that the information_schema is less likely to change than the underlying catalogs, but it's not an ironclad guarantee that your app won't break. (We haven't caught up to the SQL2003 behavior yet, but I believe Peter Eisentraut is working on it for PG 8.2.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: FW: [ADMIN] Setting up of PITR system.
Rajesh Kumar Mallah wrote: Another area i was thinking to improve this script was to make it dig out all the tablespace folders to be archived by looking into PGDATADIR/pg_tblspc . This shall make the script more generic. pg_tblspc as contents like below: $ ls -l total 0 lrwxrwxrwx 1 postgres postgres 19 Mar 27 21:45 16391 - /mnt/indexspace_new lrwxrwxrwx 1 postgres postgres 18 Mar 27 21:45 16392 - /mnt/bigtables_new can you suggest the sane/recommended way to get the destination folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf %??? ) or do i parse output of ls ! Try the readlink utility (part of GNU coreutils, not sure about other Unixen). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] semctl(): Invalid argument
What would cause the following? # psql -U pgsql -l psql: FATAL: semctl(327686, 3, SETVAL, 0) failed: Invalid argument The server is FreeBSD 6.1-PRERELEASE ... there are two jails running on it ... both jails are running PostgreSQL 7.4 ... I have the kernel built with enough semaphores: options SYSVSHM options SHMMAXPGS=199608 options SHMMAX=(SHMMAXPGS*PAGE_SIZE+1) options SYSVSEM options SEMMNI=4096 options SEMMNS=8192 options SYSVMSG # SYSV-style message queues And am definitely not using that many: (from ipcs -a): ID KEY MODEOWNERGROUPCREATOR CGROUP NSEMS OTIMECTIME s 393216 5432001 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393217 5432002 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393218 5432003 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393219 5432004 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393220 5432005 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393221 5432006 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393222 5432007 --rw--- 70 70 70 70 17 13:46:07 13:46:07 So figure there must be something else that I should be looking at ... Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Show tables query
Tom Lane wrote: Unfortunately, the SQL committee did tremendous damage to that argument by changing the definitions of some of those views in SQL2003 :-( The only changes to existing views were one renamed column and two or three cases with relaxed permission checks so that more objects are now shown. I do not expect any application to break. (We haven't caught up to the SQL2003 behavior yet, but I believe Peter Eisentraut is working on it for PG 8.2.) I'm committing it now. Thanks for reminding me -- I had almost forgotten about that patch. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] semctl(): Invalid argument
It looks like moving to FreeBSD 6.x for some of our servers is biting me in the butt ... they broke something with semaphores that used to work with 4.x, so that you can't run two postmasters on port 5432 within two seperate jails ... :( On Sun, 2 Apr 2006, Marc G. Fournier wrote: What would cause the following? # psql -U pgsql -l psql: FATAL: semctl(327686, 3, SETVAL, 0) failed: Invalid argument The server is FreeBSD 6.1-PRERELEASE ... there are two jails running on it ... both jails are running PostgreSQL 7.4 ... I have the kernel built with enough semaphores: options SYSVSHM options SHMMAXPGS=199608 options SHMMAX=(SHMMAXPGS*PAGE_SIZE+1) options SYSVSEM options SEMMNI=4096 options SEMMNS=8192 options SYSVMSG # SYSV-style message queues And am definitely not using that many: (from ipcs -a): ID KEY MODEOWNERGROUPCREATOR CGROUP NSEMS OTIMECTIME s 393216 5432001 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393217 5432002 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393218 5432003 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393219 5432004 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393220 5432005 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393221 5432006 --rw--- 70 70 70 70 17 13:46:07 13:46:07 s 393222 5432007 --rw--- 70 70 70 70 17 13:46:07 13:46:07 So figure there must be something else that I should be looking at ... Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] auto vacuuming
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: The handwriting on the wall says that autovac will soon be on by default, and perhaps become not-disablable some day after that (like the second or third time we hear from someone who's lost their data to XID wraparound after disabling it). I don't think we will see much people losing data to Xid wraparound anymore, now that the server refuses to work when the wraparound is imminent. Well, how about the second or third time we get sued by someone whose must be up 24x7 database shuts down for lack of proper vacuuming? I do think autovac is the wave of the future. The only reason it's disablable now is that we don't think we've got all the bugs out. If you read the old Berkeley Postgres papers, you'll see that a vacuum daemon was always part of the system's basic design. (Hey Elein, or anyone else who was there then --- was there ever a working vacuum daemon, or was it just on paper?) 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] Bloated pg_shdepend_depender_index
Please could someone help me with my questions below? On 3/25/06, adey [EMAIL PROTECTED] wrote: Two questions in this regard please? 1) Is tuple theory not the root of this problem 2) Vacuum does much the same as a traditional database reorg, and online reorgs are a reality now 1) If I understand tuple theory correctly, copies of rows are created through normal Postgres processing, that expire after a period, leaving multiple copies of redundant data to be vacuumed dead, then vacuumed out (or both). Most databases have been built using one copy of a row with sophisticated locking control mechanism that Postgres has some of anyway, and the industry has developed methods and designsto exploit locking to best advantage. Even with tuples, locking is still evident in Postgres. OR 2) Can vacuum fullnot be redesigned to run online without locking tables and users, like a conventional online reorg, eg: work on 1 data page at a time instead of locking the whole table with a shorter period at the end to lock the table and compress the remaining populated data pages and release disk space back to the OS; or one data file at a time, and have vacuum full per table reduce / tidy upthe wraparound value, thereby avoiding a full DB vacuum for longer periods. In this way vacuum can be performed regularly and be less intrusive. Nowadays 24x7 is more of a reality for systems and we can't afford to take systems down for many hours to perform regular maintenance. (It would be extremely helpful to DBA's with little OS experience or accessto have more automation in PGAdmin, especially task scheduling and alerting, so SQL can be scheduled in PGAmin instead of crontab, which is usually a sysadmin function). On 3/25/06, Tom Lane [EMAIL PROTECTED] wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Am Freitag, 24. März 2006 05:48 schrieb Tom Lane: Well, the VACUUM FULL algorithm is incapable of shrinking indexes --- the only way is REINDEX, or something else that reconstructs indexes from scratch, such as CLUSTER.One of the things we need to look into is putting more smarts into VACUUM so that it automatically does something reasonable when faced with extreme cases like these. If the user is running VACUUM FULL, he has presumably determined that the table is too bloated to be recovered in a graceful way, and quite likely the indexes are going to be bloated similarly.So seemingly one might as well launch a reindexing on the table after VACUUM FULL has done its thing. Whether that should be automatic is another question but perhaps the advice should be documented somewhere?Actually, I wonder whether VACUUM FULL shouldn't be thrown away and replaced by something else entirely.That algorithm only really works nicely when just a small percentage of the rows need to be moved tore-compact the table --- if you're moving lots of rows, it makes the index bloat situation *worse* not better because of the transient needfor index entries pointing to both copies of moved rows.Lazy VACUUMhas become the de-facto standard for situations where there's not a huge amount of empty space, and so it's not clear where the sweet spot is for VACUUM FULL anymore.If you've got enough disk space, a rewrite (likeCLUSTER or ALTER TABLE) is going to blow the doors off VACUUM FULL, let alone VACUUM FULL plus REINDEX.Not to mention that forsufficiently huge tables, VACUUM FULL fails outright because it runs out of RAM.We need to fix CLUSTER to make it MVCC-safe (ie, not discardrecently-dead rows), and it'd be nice to have something like it that didn't worry about ordering but just did a seqscan of the source table. Then I'd be inclined to recommend that instead of VACUUM FULL for mostcases of severe bloat.Unfortunately this all breaks down for shared system catalogs and the core (nailed-in) catalogs, because we can't change their relfilenodes and so the crash-safe CLUSTER/REINDEX approach doesn't work.We stillneed a new idea or two there. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Setting up of PITR system.
Whenever you get a finalized script including all the enhancements
you've been talking about,
I would really love to have a copy as I'm sure lots of people here
would. I think this is
something that should be included in the standard distribution.
Can I make a couple of suggestions?
1. Include a mail option to send the admin an email when the backup
succeeds or fails.
2. Call pg_stop_backup() if the script fails for whatever reason. You
can't run the script
twice unless you stop the backup. I ran the script a few times to
see if I can get a
PITR backup system going and due to directory permissions and
such and during basic
testing, I had to manually call pg_stop_backup() after the script
exits abnormally.
Thanks very much for building this script and sharing it with us.
Many of us don't have the skills to
write such a script. I'm a Java programmer, but I have little bash
scripting skills, so this
is very much appreciated.
Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED]
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On Apr 2, 2006, at 10:14 AM, Rajesh Kumar Mallah wrote:
On 4/2/06, Grega Bremec [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Rajesh Kumar Mallah wrote:
|
| Instead of taking a round about method i am using the content of
the
| file, (This was also suggested by Andy at somepoint)
After reading the docs again, that's what I would do as well,
indeed. :)
| lately i feel that we should not be discussing the topic over here
| becoz it has less to do with postgresql and more of bash.
I've been considering that seriously in the very first post I
wrote, but
since there seems to be a lot of people interested in a working,
flexible WAL archiving script, I decided to keep it on the list.
It is
an administration issue, afterall. I will stand corrected if someone
feels we're clogging their mailboxes.
| ##
| #START WAL LOCATION: E/A9145E4 (file 0001000E000A)
| #CHECKPOINT LOCATION: E/A92939C
| #START TIME: 2006-04-01 14:36:48 IST
| #LABEL: base_backup_01-04-2006-14-36-45
| ###
|
| BACKUP_LABEL=$DATADIR/backup_label
| # get the like containing line START WAL LOCATION
|
| START_LINE=`grep -i START WAL LOCATION $BACKUP_LABEL`
| # strip something like 'START WAL LOCATION: E/A9145E4 (file '
from begin.
| START_LINE=${START_LINE/#START*file /}
| # strip ')' from end.
| START_LINE=${START_LINE/%)/}
| # REF_FILE_NUM is something like 0001000A0068
| REF_FILE_NUM=$START_LINE
Why not go for the entire filename?
it takes a while (i dont know how much) for the .backup file to get
archived and appear in the wal archive area. thats why i prefer to
use the wal log filename (0001000A0068) instead of
something like 0001000A0068.0A348A45.backup.
Do you see any problem in the current approach ?
i have seen it working fine till now.
Another area i was thinking to improve this script was to
make it dig out all the tablespace folders to be archived by looking
into PGDATADIR/pg_tblspc . This shall make the script more generic.
pg_tblspc as contents like below:
$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 19 Mar 27 21:45 16391 - /mnt/
indexspace_new
lrwxrwxrwx 1 postgres postgres 18 Mar 27 21:45 16392 - /mnt/
bigtables_new
can you suggest the sane/recommended way to get the destination
folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf
%??? )
or do i parse output of ls !
if above is done i see the script INPUT/OUTPUT'ACTIVITY as below
INPUTS: PGDATADIR , WAL_ARCHIVE_DIR , LOCAL DUMP DIRECTORY
OUTPUT: n/a
ACTIVITY:
1. it shall take a base backup of PGDATADIR (minus pg_xlog) and
all tablespaces into *LOCAL* DUMP Directory
2. Frees disk space by removing unwanted LOG files in WAL_ARCHIVE_DIR
i am waiting for my another machine to get fitted with 2 more drives
so that i can test/develop scripts for the restoration part and modify
the script for remote base backups and archiving. that shall
happen in a week or so.
thanks for your support till now.
Regds
Rajesh Kumar Mallah.
Kind regards,
- --
~Grega Bremec
~gregab at p0f dot net
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
iD8DBQFEL9x/fu4IwuB3+XoRA3IgAJ9Qn7dYsNhv3e9f+P64mJoiz+s77gCeLELY
4xAxFb3Ncd8RHWkBbgyag7U=
=7MXQ
-END PGP SIGNATURE-
---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
smime.p7s
Description: S/MIME cryptographic signature
Re: [ADMIN] Setting up of PITR system.
On 4/3/06, Brendan Duddridge [EMAIL PROTECTED] wrote:
Whenever you get a finalized script including all the enhancements
you've been talking about,
I would really love to have a copy as I'm sure lots of people here
would. I think this is
something that should be included in the standard distribution.
Can I make a couple of suggestions?
1. Include a mail option to send the admin an email when the backup
succeeds or fails.
Considering the fact that the script is hugely unix oriented currently,
such a facility is anyway avialble in crontab using MAILTO variable.
2. Call pg_stop_backup() if the script fails for whatever reason. You
can't run the script
twice unless you stop the backup. I ran the script a few times to
see if I can get a
PITR backup system going and due to directory permissions and
such and during basic
testing, I had to manually call pg_stop_backup() after the script
exits abnormally.
yes this is a problem , i do not know if calling pg_stop_backup() is the
end to the damage control steps, but i shall surely add it for the time
being.
Thanks very much for building this script and sharing it with us.
Many of us don't have the skills to
write such a script. I'm a Java programmer, but I have little bash
scripting skills, so this
is very much appreciated.
Thanks to prying eyes of the bash gurus who are supervising it :)
i only have the machines and situation to run it and seek
suggestions.
Regds
Rajesh Kumar Mallah.
Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED]
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On Apr 2, 2006, at 10:14 AM, Rajesh Kumar Mallah wrote:
On 4/2/06, Grega Bremec [EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Rajesh Kumar Mallah wrote:
|
| Instead of taking a round about method i am using the content of
the
| file, (This was also suggested by Andy at somepoint)
After reading the docs again, that's what I would do as well,
indeed. :)
| lately i feel that we should not be discussing the topic over here
| becoz it has less to do with postgresql and more of bash.
I've been considering that seriously in the very first post I
wrote, but
since there seems to be a lot of people interested in a working,
flexible WAL archiving script, I decided to keep it on the list.
It is
an administration issue, afterall. I will stand corrected if someone
feels we're clogging their mailboxes.
| ##
| #START WAL LOCATION: E/A9145E4 (file 0001000E000A)
| #CHECKPOINT LOCATION: E/A92939C
| #START TIME: 2006-04-01 14:36:48 IST
| #LABEL: base_backup_01-04-2006-14-36-45
| ###
|
| BACKUP_LABEL=$DATADIR/backup_label
| # get the like containing line START WAL LOCATION
|
| START_LINE=`grep -i START WAL LOCATION $BACKUP_LABEL`
| # strip something like 'START WAL LOCATION: E/A9145E4 (file '
from begin.
| START_LINE=${START_LINE/#START*file /}
| # strip ')' from end.
| START_LINE=${START_LINE/%)/}
| # REF_FILE_NUM is something like 0001000A0068
| REF_FILE_NUM=$START_LINE
Why not go for the entire filename?
it takes a while (i dont know how much) for the .backup file to get
archived and appear in the wal archive area. thats why i prefer to
use the wal log filename (0001000A0068) instead of
something like 0001000A0068.0A348A45.backup.
Do you see any problem in the current approach ?
i have seen it working fine till now.
Another area i was thinking to improve this script was to
make it dig out all the tablespace folders to be archived by looking
into PGDATADIR/pg_tblspc . This shall make the script more generic.
pg_tblspc as contents like below:
$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 19 Mar 27 21:45 16391 - /mnt/
indexspace_new
lrwxrwxrwx 1 postgres postgres 18 Mar 27 21:45 16392 - /mnt/
bigtables_new
can you suggest the sane/recommended way to get the destination
folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf
%??? )
or do i parse output of ls !
if above is done i see the script INPUT/OUTPUT'ACTIVITY as below
INPUTS: PGDATADIR , WAL_ARCHIVE_DIR , LOCAL DUMP DIRECTORY
OUTPUT: n/a
ACTIVITY:
1. it shall take a base backup of PGDATADIR (minus pg_xlog) and
all tablespaces into *LOCAL* DUMP Directory
2. Frees disk space by removing unwanted LOG files in WAL_ARCHIVE_DIR
i am waiting for my another machine to get fitted with 2 more drives
so that i can test/develop scripts for the restoration part and modify
the script for remote base backups and archiving. that shall
happen in a week or so.
thanks for your support till
