[ADMIN] performance problem - 10.000 databases
Hi all We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) I've made some test's - 3000 databases and 400 clients connected at same time. These clients was doing on each database set of query 10 times, and then connect to next database. These queries: select * from table1 where number='$i' update table2 set some_text='int(rand(5))' select * from table1 where position in (select position from table2 where number in (select number from table3)) Each database has four tables (int,text,int) with 1000 records. Postgres is taking all memory and all processor ( 4CPU with Hyper Threading ) The first two queries has time duration 0 to 10 sec Third query has 15-70 sec. But my problem is that when I hit command: psql -h 127.0.0.1 dbname dbuser I'm waiting about 3-5 sec to enter psql monitor, so every new connection from apache will wait about 3-5 sec to put query to server. Thats a very long time... 4 sec. to connect to server and 4 sec. to process a query. Why this time to connect to server is so long ??? I could made persistent connection, but with 10.000 clients it will kill the server. Has any one idea how to tune postgres, to accept connection faster? Maybe some others settings to speed up server ? My settings: PostgreSQL: max_connections = 512 shared_buffers = 8192 max_fsm_relations = 1 max_fsm_pages = 10 max_locks_per_transaction = 512 wal_buffers = 32 sort_mem = 327681 vacuum_mem = 8192 fsync = true effective_cache_size = 100 log_connections = true log_pid = true log_statement = true log_duration = true log_timestamp = true Kernel: kernel.shmmni = 8192 kernel.shmall = 134217728 kernel.shmmax = 536870912 RLIMIT_NPROC=1000 greetings Marek ---(end of broadcast)--- TIP 3: 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] performance problem - 10.000 databases
I could made persistent connection, but with 10.000 clients it will kill the server. But if they're virtual domains, why would you need one connection per domain? You should only need one connection per apache process... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 11:52, Matt Clark pisze: I could made persistent connection, but with 10.000 clients it will kill the server. But if they're virtual domains, why would you need one connection per domain? You should only need one connection per apache process... Because every virtual domain has its own database, username and password. So one client domain1.com with db: domain1db user: domain1user cannot access to second client database domain2.com db: domain2db user: domain2user pg_hba.conf look like this: hostdomain1db domain1user ip_addr netmask md5 hostdomain2db domain2user ip_addr netmask md5 . 10.000 records ... ... hostdomain1db domain1user ip_addr netmask md5 You know, one client cannot access to other client database. So, if one process is connected to domain1db it cannto access to domain2db, and others. greetings Marek ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] performance problem - 10.000 databases
Marek Florianczyk wrote: But my problem is that when I hit command: psql -h 127.0.0.1 dbname dbuser I'm waiting about 3-5 sec to enter psql monitor, so every new connection from apache will wait about 3-5 sec to put query to server. Thats a very long time... Why don't you use a connection manager ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 12:25, Matt Clark pisze: Ooh, I see. That's a tricky one. Do you really need that level of separation? Well, if you talk with the clients, and they promise, that they will not access to other databasess, and specially don't do drop database my_bes_fried_db I can put: hostany any 0.0.0.0 0.0.0.0 trust in the very beginning of pg_hba.conf ;) greetings ;) Marek Because every virtual domain has its own database, username and password. So one client domain1.com with db: domain1db user: domain1user cannot access to second client database domain2.com db: domain2db user: domain2user pg_hba.conf look like this: hostdomain1db domain1user ip_addr netmask md5 hostdomain2db domain2user ip_addr netmask md5 . 10.000 records ... ... hostdomain1db domain1user ip_addr netmask md5 You know, one client cannot access to other client database. So, if one process is connected to domain1db it cannto access to domain2db, and others. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: 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] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 12:25, Matt Clark pisze: Ooh, I see. That's a tricky one. Do you really need that level of separation? Well, if you talk with the clients, and they promise, that they will not access to other databasess, and specially don't do drop database my_bes_fried_db I can put: host any any 0.0.0.0 0.0.0.0 trust in the very beginning of pg_hba.conf ;) I was more thinking that it might be possible to manage the security at a different level than the DB. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] performance problem - 10.000 databases
On Fri, 31 Oct 2003, Matt Clark wrote: I was more thinking that it might be possible to manage the security at a different level than the DB. We do this with users and permissions. Each virtual host has an apache config include specifying a db user, pass (and database, although most of them use the same one). Permissions on the database tables are set so that a given vhost can only access their own data. Our setup is mod_perl. Don't know how one would go about doing this with PHP, but I imagine it has some mechanism for per-vhost variables or similar. -j -- Jamie Lawrence[EMAIL PROTECTED] Remember, half-measures can be very effective if all you deal with are half-wits. - Chris Klein ---(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] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 13:54, Jamie Lawrence pisze: On Fri, 31 Oct 2003, Matt Clark wrote: I was more thinking that it might be possible to manage the security at a different level than the DB. We do this with users and permissions. Each virtual host has an apache config include specifying a db user, pass (and database, although most of them use the same one). Permissions on the database tables are set so that a given vhost can only access their own data. Our setup is mod_perl. Don't know how one would go about doing this with PHP, but I imagine it has some mechanism for per-vhost variables or similar. So, as I understand apache vhost can only connect to specified database. Strange... no PHP only mod_perl that fetch data from database and writes html document ? So, clients don't make any scripts, and don't use function like pgconnect? Do they use CGI with mod_perl, and they write scripts in perl ? Interesting. Don't know if it's possible with PHP, don't think so. But... If I would have 200, or even 900 clients I would do apache with vhost. But when I have 10.000 clients, apache cannot work with vhosts. ( some system limitation ) So we use our own dynamic vhost module. When request is made to server, it checks domain part of the request, and search i LDAP what is DocumentRoot for that domain, and then return proper file. Config looks like it was only one vhost, but it works with 10.000 domains ;) No, I think that your solution, would not work for us. Everything is complicated when a large number of anything occurs. ;) greetings sorry for my bad english ---(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] performance problem - 10.000 databases
Marek Florianczyk [EMAIL PROTECTED] writes: We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) I've made some test's - 3000 databases and 400 clients connected at same time. You are going to need much more serious iron than that if you want to support 1 active databases. The required working set per database is a couple hundred K just for system catalogs (I don't have an exact figure in my head, but it's surely of that order of magnitude). So the system catalogs alone would require 2 gig of RAM to keep 'em swapped in; never mind caching any user data. The recommended way to handle this is to use *one* database and create 1 users each with his own schema. That should scale a lot better. Also, with a large max_connections setting, you have to beware that your kernel settings are adequate --- particularly the open-files table. It's pretty easy for Postgres to eat all your open files slots. PG itself will usually survive this condition just fine, but everything else you run on the machine will start falling over :-(. For safety you should make sure that max_connections * max_files_per_process is comfortably less than the size of the kernel's open-files table. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] performance problem - 10.000 databases
Hmm, maybe you need to back off a bit here on your expectations. You said your test involved 400 clients simultaneously running queries that hit pretty much all the data in each client's DB. Why would you expect that to be anything *other* than slow? And does it reflect expected production use? Unless those 10,000 sites are all fantastically popular, surely it's more likely that only a small number of queries will be in progress at any given time? You're effectively simulating running 400 _very_ popular dynamic websites off one 2-cpu DB server. You also said that CPU is pegged at 100%. Given that you've got 400 backends all competing for CPU time you must have an insane load average too, so improving the connect time might prove to be of no use, as you could well just get fasert connects and then slower queries! Sorry this email wasn't more constructive ;-) M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Marek Florianczyk Sent: 31 October 2003 13:20 To: Jamie Lawrence Cc: Matt Clark; [EMAIL PROTECTED] Subject: Re: [ADMIN] performance problem - 10.000 databases W licie z pi, 31-10-2003, godz. 13:54, Jamie Lawrence pisze: On Fri, 31 Oct 2003, Matt Clark wrote: I was more thinking that it might be possible to manage the security at a different level than the DB. We do this with users and permissions. Each virtual host has an apache config include specifying a db user, pass (and database, although most of them use the same one). Permissions on the database tables are set so that a given vhost can only access their own data. Our setup is mod_perl. Don't know how one would go about doing this with PHP, but I imagine it has some mechanism for per-vhost variables or similar. So, as I understand apache vhost can only connect to specified database. Strange... no PHP only mod_perl that fetch data from database and writes html document ? So, clients don't make any scripts, and don't use function like pgconnect? Do they use CGI with mod_perl, and they write scripts in perl ? Interesting. Don't know if it's possible with PHP, don't think so. But... If I would have 200, or even 900 clients I would do apache with vhost. But when I have 10.000 clients, apache cannot work with vhosts. ( some system limitation ) So we use our own dynamic vhost module. When request is made to server, it checks domain part of the request, and search i LDAP what is DocumentRoot for that domain, and then return proper file. Config looks like it was only one vhost, but it works with 10.000 domains ;) No, I think that your solution, would not work for us. Everything is complicated when a large number of anything occurs. ;) greetings sorry for my bad english ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 15:23, Tom Lane pisze: Marek Florianczyk [EMAIL PROTECTED] writes: We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) I've made some test's - 3000 databases and 400 clients connected at same time. You are going to need much more serious iron than that if you want to support 1 active databases. The required working set per database is a couple hundred K just for system catalogs (I don't have an exact figure in my head, but it's surely of that order of magnitude). it's about 3.6M So the system catalogs alone would require 2 gig of RAM to keep 'em swapped in; never mind caching any user data. The recommended way to handle this is to use *one* database and create 1 users each with his own schema. That should scale a lot better. Also, with a large max_connections setting, you have to beware that your kernel settings are adequate --- particularly the open-files table. It's pretty easy for Postgres to eat all your open files slots. PG itself will usually survive this condition just fine, but everything else you run on the machine will start falling over :-(. For safety you should make sure that max_connections * max_files_per_process is comfortably less than the size of the kernel's open-files table. Yes, I have made some updates, number of process, semaphores, and file descriptor. I'm aware of this limitation. On this machine there will be only PostgreSQL, nothing else. This idea with one database and 10.000 schemas is very interesting, I never thought about that. I will make some tests on monday and send results to the list. greeings Marek ---(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] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 15:30, Matt Clark pisze: Hmm, maybe you need to back off a bit here on your expectations. You said your test involved 400 clients simultaneously running queries that hit pretty much all the data in each client's DB. Why would you expect that to be anything *other* than slow? And does it reflect expected production use? Unless those 10,000 sites are all fantastically popular, surely it's more likely that only a small number of queries will be in progress at any given time? You're effectively simulating running 400 _very_ popular dynamic websites off one 2-cpu DB server. Well, maybe these queries will not happens in production life, but if many clients will make large tables and no index, effect can be this same. Besides I wanted to identify thin throat on this machine before we will put this to the production. PostgreSQL was working quite good, and if not this long time to connect to database I would be quite happy. But solution from Tom is great I think, so I must test it. have nice weekend ! Marek You also said that CPU is pegged at 100%. Given that you've got 400 backends all competing for CPU time you must have an insane load average too, so improving the connect time might prove to be of no use, as you could well just get fasert connects and then slower queries! Sorry this email wasn't more constructive ;-) M -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Marek Florianczyk Sent: 31 October 2003 13:20 To: Jamie Lawrence Cc: Matt Clark; [EMAIL PROTECTED] Subject: Re: [ADMIN] performance problem - 10.000 databases W licie z pi, 31-10-2003, godz. 13:54, Jamie Lawrence pisze: On Fri, 31 Oct 2003, Matt Clark wrote: I was more thinking that it might be possible to manage the security at a different level than the DB. We do this with users and permissions. Each virtual host has an apache config include specifying a db user, pass (and database, although most of them use the same one). Permissions on the database tables are set so that a given vhost can only access their own data. Our setup is mod_perl. Don't know how one would go about doing this with PHP, but I imagine it has some mechanism for per-vhost variables or similar. So, as I understand apache vhost can only connect to specified database. Strange... no PHP only mod_perl that fetch data from database and writes html document ? So, clients don't make any scripts, and don't use function like pgconnect? Do they use CGI with mod_perl, and they write scripts in perl ? Interesting. Don't know if it's possible with PHP, don't think so. But... If I would have 200, or even 900 clients I would do apache with vhost. But when I have 10.000 clients, apache cannot work with vhosts. ( some system limitation ) So we use our own dynamic vhost module. When request is made to server, it checks domain part of the request, and search i LDAP what is DocumentRoot for that domain, and then return proper file. Config looks like it was only one vhost, but it works with 10.000 domains ;) No, I think that your solution, would not work for us. Everything is complicated when a large number of anything occurs. ;) greetings sorry for my bad english ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] performance problem - 10.000 databases
On Friday 31 October 2003 09:59 am, Marek Florianczyk wrote: W licie z pi, 31-10-2003, godz. 15:23, Tom Lane pisze: Marek Florianczyk [EMAIL PROTECTED] writes: We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) I've made some test's - 3000 databases and 400 clients connected at same time. You are going to need much more serious iron than that if you want to support 1 active databases. The required working set per database is a couple hundred K just for system catalogs (I don't have an exact figure in my head, but it's surely of that order of magnitude). it's about 3.6M So the system catalogs alone would require 2 gig of RAM to keep 'em swapped in; never mind caching any user data. The recommended way to handle this is to use *one* database and create 1 users each with his own schema. That should scale a lot better. Also, with a large max_connections setting, you have to beware that your kernel settings are adequate --- particularly the open-files table. It's pretty easy for Postgres to eat all your open files slots. PG itself will usually survive this condition just fine, but everything else you run on the machine will start falling over :-(. For safety you should make sure that max_connections * max_files_per_process is comfortably less than the size of the kernel's open-files table. Yes, I have made some updates, number of process, semaphores, and file descriptor. I'm aware of this limitation. On this machine there will be only PostgreSQL, nothing else. This idea with one database and 10.000 schemas is very interesting, I never thought about that. I will make some tests on monday and send results to the list. Following this logic, if you are willing to place the authentication in front of the database instead of inside it you can use a connection pool and simply change the search_path each time a new user accesses the database. greeings Marek ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Mike Rylander ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] performance problem - 10.000 databases
W licie z pi, 31-10-2003, godz. 16:51, Mike Rylander pisze: On Friday 31 October 2003 09:59 am, Marek Florianczyk wrote: W licie z pi, 31-10-2003, godz. 15:23, Tom Lane pisze: Marek Florianczyk [EMAIL PROTECTED] writes: We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) I've made some test's - 3000 databases and 400 clients connected at same time. You are going to need much more serious iron than that if you want to support 1 active databases. The required working set per database is a couple hundred K just for system catalogs (I don't have an exact figure in my head, but it's surely of that order of magnitude). it's about 3.6M So the system catalogs alone would require 2 gig of RAM to keep 'em swapped in; never mind caching any user data. The recommended way to handle this is to use *one* database and create 1 users each with his own schema. That should scale a lot better. Also, with a large max_connections setting, you have to beware that your kernel settings are adequate --- particularly the open-files table. It's pretty easy for Postgres to eat all your open files slots. PG itself will usually survive this condition just fine, but everything else you run on the machine will start falling over :-(. For safety you should make sure that max_connections * max_files_per_process is comfortably less than the size of the kernel's open-files table. Yes, I have made some updates, number of process, semaphores, and file descriptor. I'm aware of this limitation. On this machine there will be only PostgreSQL, nothing else. This idea with one database and 10.000 schemas is very interesting, I never thought about that. I will make some tests on monday and send results to the list. Following this logic, if you are willing to place the authentication in front of the database instead of inside it you can use a connection pool and simply change the search_path each time a new user accesses the database. Well it's not so simple, I think. If I've got apache+php+phpAccelerator with persistent connection on. Server holds some pool of connection, but when new request is made from phpscripts, apache looks at his connection pool for a connection with parameters: dbname,dbuser,dbserver. So for each of 10.000 virtual domain ( 10.000 databases ) I would have to hold such a connection. Second thing: How to change search_path dynamically ? I can set in postgresql.conf: search_path '$user, public' but it works when a new client (username,password) is connecting to server, it gets his own schema with proper privileges (USE,CREATE) and thats all. Right ? Or maybe I don't uderstand something ? If I will do in pg_hba.conf only one record: hostany any ip_addr netmask md5 and only one database, I must make 10.000 schemas with proper accesslist (USE,CREATE only for one user, and schemaname is same as dbusername) This is what I want to test ;) Now user connect from phpscript with dbusername=unique_user dbpass=unique_pass dbname=shared_db Server holds persistent connection, but it's still one connection per user, so it would have to have 10.000 simultaneous connection. I can't see any benefits, with connection pool, or I did not understand what you wanted to tell me. How to place authentication in front of the database using, when clients are using phpscripts ? greetings Marek greeings Marek ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] fishing out LOG: duration lines from the logfile.
Hi, I think its not going to a trivial task to take out only the LOG duration lines from a PostgreSQL logfile. We need to extract the duration and the actual statement. I think i will put a custom delimeters around the statements for the time being so that the log parser can parse it unambigiously. Is there any better method? Regds Mallah. Example Log file: LOG: duration: 3.725 ms statement: SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^users$' ORDER BY 2, 3; other log messages other log messages other log messages LOG: duration: 0.705 ms statement: SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_catalog.pg_class WHERE oid = '17411' LOG: duration: 5.929 ms statement: SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '17411' AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum LOG: duration: 0.799 ms statement: SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = '17411' AND d.adnum = 1 LOG: duration: 0.965 ms statement: SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = '17411' AND d.adnum = 31 LOG: duration: 0.998 ms statement: SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = '17411' AND d.adnum = 33 LOG: duration: 2.288 ms statement: SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = '17411' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname LOG: duration: 2.288 ms statement: SELECT c2.relname, i.indisprimary, i.indisunique, pg_catalog.pg_get_indexdef(i.indexrelid) FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = '17411' AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname :statement end (-- custom delimit)
Re: [ADMIN] performance problem - 10.000 databases
We have a similar issue regarding security. Some of the access to our database will be by ODBC connections for reporting purposes (ie. Actuate Report/Crystal Reports). Without creating a zillion or so views (which I suspect carries with it alot of overhead), I believe it would be tricky to maintain security. Our application is medical related, and we are bound by HIPAA rules, so security is most important. How would you architect this scenario so our ASP customers cannot see each others data? Naomi I was more thinking that it might be possible to manage the security at a different level than the DB. We do this with users and permissions. Each virtual host has an apache config include specifying a db user, pass (and database, although most of them use the same one). Permissions on the database tables are set so that a given vhost can only access their own data. Our setup is mod_perl. Don't know how one would go about doing this with PHP, but I imagine it has some mechanism for per-vhost variables or similar. -j -- Jamie Lawrence[EMAIL PROTECTED] Remember, half-measures can be very effective if all you deal with are half-wits. - Chris Klein ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) - Naomi Walker Chief Information Officer Eldorado Computing, Inc. [EMAIL PROTECTED] 602-604-3100 - Insanity is doing things in the same way and expecting different results. -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] Duplicating log lines from postmaster
I am seeing strange behavior with my postmaster logs, it seems to be writing out each line of a log entry 10 times. This seems to have started recently without any know config changes. I am running 7.3.2 on RedHat 7.3 i386. Below is a snippet from the logs. Thanks in advance for any help. Thanks, Justin Cragin Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [1] ERROR: parser: parse error at or near AND at character 82 Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-1] LOG: statement: UPDATE mxl_key_sub SET active = 0 WHERE domain_id = 8023409 AND key_bucket_id = AND scope = '2' AND (dir='I' Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) Oct 28 10:04:18 p01d187 postgres[11125]: [2-2] OR dir is null) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] Error compiling 7.3.4 on Solaris 9
I am having problems compiling Postgres 7.3.4 on a Sun Fire V120
running Solaris 9.
./configure --with-java --with-openssl=/usr/local/ssl --enable-syslog --disable-shared --enable-locale --enable-multibyte
Configure goes OK, but when #gmake following error:
make[2]: Entering directory `~/src/postgresql-7.3.4/src/interfaces'
make[3]: Entering directory `~/src/postgresql-7.3.4/src/interfaces/libpq'
gcc -Wall -Wmissing-prototypes -Wmissing-declarations -fPIC -I. -I../../../src/include
-I/usr/local/ssl/include -DFRONTEND -DSYSCONFDIR='/usr/local/pgsql/etc' -c -o
fe-connect.o fe-connect.c
In file included from fe-connect.c:46:
/usr/include/crypt.h:22: parse error before `('
/usr/include/crypt.h:22: parse error before `const'
make[3]: *** [fe-connect.o] Error 1
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [ADMIN] pg_clog vacuum oddness
[EMAIL PROTECTED] (Jeff) writes: On Wed, 29 Oct 2003 11:53:38 -0500 DHS Webmaster [EMAIL PROTECTED] wrote: We vacuum our working database nightly. Although this is not a 'full', we don't exclude any tables. We don't do anything with template1 (knowingly), so we do not perform any maintenance on it either. Why not go through the list in pg_database to make sure you didn't forget about any (like I did). given that template0 and 1 rarely change.. I don't see why we'd need to vacuum them template0 is probably set to 'not changeable' so that you can't even log in to it. template1 probably isn't hit a _lot_, but surely not not at all. It is accessed at such times as: - When you run createdb, data probably gets used from there to populate the new DB. - When you update user IDs, that's shared information likely to touch template1. You don't need to vacuum it often, but seldom is not quite the same as never. -- output = (cbbrowne @ libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] PG_DUMP Question
Hi, I have a test system that is setup the same as a production system and would like to frequently copy the database over. pg_dump takes a few hours and even sometimes hangs. Are there any reasons not to simply just copy the entire data directory over to the test system? I could not find any postings on the net suggesting otherwise. Is there anything to pay attention too ? Thanks for any advise Alex ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Employment Opportunity in San Jose, Ca
DBA's, 2Wire is looking for a DBA with PostgreSQL Exp...if you know of someone or if your looking for a new opportunity then please send your resume or call so we can discuss the position. Please send you resume to [EMAIL PROTECTED] Thank you, Gregg Lynch Sr. Contract Recruiter 2Wire Inc. Direct (408) 503-1022 Fax (408) 428-9590 [EMAIL PROTECTED] www.2wire.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] dumping tables from badly damaged db
Recently I had a problem where a system crash scribbed on some directories, which landed a bunch of files, including a few of the system table files for one of my databases, in lost+found along with a zillion other files. I might be able to find the file for this table/index in lost+found, but how do I know what name to give it in /var/lib/postgres/...? These files are named after OID's which I can't find because I can't connect to the database. If I can't get the file back, is there any way I can dump the data out of the remaining tables? Currently, I can't connect to the database with the following error: psql: FATAL 1: cannot open pg_class_relname_index: No such file or directory I suspect that's very bad, considering the centrality of the pg_class table... -- Brian Ristuccia [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Error compiling 7.3.4 on Solaris 9
=?windows-1251?Q?=CA=F0=E8=E2=EE=F8=E5=E5=E2_=CF=E0=E2=E5=EB?= [EMAIL PROTECTED] writes: I am having problems compiling Postgres 7.3.4 on a Sun Fire V120 running Solaris 9. Take out the inclusion of crypt.h in fe-connect.c. This probably should be back-patched into 7.3.5: 2003-06-23 13:03 momjian * src/interfaces/libpq/fe-connect.c: Remove crypt.h from fe-connect.c --- not needed, and caused problems on Solaris with Open SSL version 0.9.7b regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Postgres Table Size
I have a table like the following
create table test {
id int8,
lastupdate date,
balance numeric(12, 2) };
With an index:
create index ix_test(id, lastupdate);
This table currently has 6 million records. I have done a vacuum full
and reindex this morning. The file associated with this table is 1g
on disk. This seems a lot high to me? Am I wrong, any help would be
greatly appreciated.
Thank You,
Bo Stewart
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [ADMIN] performance problem - 10.000 databases
On 31 Oct 2003, Marek Florianczyk wrote: Hi all We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) Tom's right, you need more memory, period, and probably want a very large RAID1+0 (with like 10 or more disks). Has any one idea how to tune postgres, to accept connection faster? Postgresql will take the amount of time it needs. Connections, especially in a contentious environment, aren't cheap. Maybe some others settings to speed up server ? My settings: PostgreSQL: max_connections = 512 shared_buffers = 8192 max_fsm_relations = 1 max_fsm_pages = 10 max_locks_per_transaction = 512 wal_buffers = 32 sort_mem = 327681 -^^-- THIS IS WAY TOO HIGH. That's ~320Meg! PER SORT. Drop this down to something reasonable like 8192 or something. (i.e. 8 meg) If there were lots of big sorts going on by all 300 users, then that's 300*320 Meg memory that could get used up. I.e. swap storm. Have you adjusted random_page_cost to reflect your I/O setup? While the default of 4 is a good number for a single drive server, it's kinda high for a machine with 4 or more drives in an array. Figures from 1.2 to 2.0 seem common. My database under 7.2.4 run best with about 1.4 random_page_cost ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] performance problem - 10.000 databases
On Friday 31 October 2003 11:19 am, Marek Florianczyk wrote: W licie z pi, 31-10-2003, godz. 16:51, Mike Rylander pisze: On Friday 31 October 2003 09:59 am, Marek Florianczyk wrote: W licie z pi, 31-10-2003, godz. 15:23, Tom Lane pisze: Marek Florianczyk [EMAIL PROTECTED] writes: We are building hosting with apache + php ( our own mod_virtual module ) with about 10.000 wirtul domains + PostgreSQL. PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM scsi raid 1+0 ) I've made some test's - 3000 databases and 400 clients connected at same time. You are going to need much more serious iron than that if you want to support 1 active databases. The required working set per database is a couple hundred K just for system catalogs (I don't have an exact figure in my head, but it's surely of that order of magnitude). it's about 3.6M So the system catalogs alone would require 2 gig of RAM to keep 'em swapped in; never mind caching any user data. The recommended way to handle this is to use *one* database and create 1 users each with his own schema. That should scale a lot better. Also, with a large max_connections setting, you have to beware that your kernel settings are adequate --- particularly the open-files table. It's pretty easy for Postgres to eat all your open files slots. PG itself will usually survive this condition just fine, but everything else you run on the machine will start falling over :-(. For safety you should make sure that max_connections * max_files_per_process is comfortably less than the size of the kernel's open-files table. Yes, I have made some updates, number of process, semaphores, and file descriptor. I'm aware of this limitation. On this machine there will be only PostgreSQL, nothing else. This idea with one database and 10.000 schemas is very interesting, I never thought about that. I will make some tests on monday and send results to the list. Following this logic, if you are willing to place the authentication in front of the database instead of inside it you can use a connection pool and simply change the search_path each time a new user accesses the database. Well it's not so simple, I think. If I've got apache+php+phpAccelerator with persistent connection on. Server holds some pool of connection, but when new request is made from phpscripts, apache looks at his connection pool for a connection with parameters: dbname,dbuser,dbserver. So for each of 10.000 virtual domain ( 10.000 databases ) I would have to hold such a connection. Second thing: How to change search_path dynamically ? I can set in postgresql.conf: search_path '$user, public' but it works when a new client (username,password) is connecting to server, it gets his own schema with proper privileges (USE,CREATE) and thats all. Right ? search_path documentation is here: http://www.postgresql.org/docs/view.php?version=7.3idoc=1file=ddl-schemas.html Or maybe I don't uderstand something ? If I will do in pg_hba.conf only one record: host any any ip_addr netmask md5 and only one database, I must make 10.000 schemas with proper accesslist (USE,CREATE only for one user, and schemaname is same as dbusername) This is what I want to test ;) Now user connect from phpscript with dbusername=unique_user dbpass=unique_pass dbname=shared_db Server holds persistent connection, but it's still one connection per user, so it would have to have 10.000 simultaneous connection. I can't see any benefits, with connection pool, or I did not understand what you wanted to tell me. How to place authentication in front of the database using, when clients are using phpscripts ? I suppose I didn't really explain what I was thinking. The senario I was thinking of would go something like this: User logins (ssh, etc...) if available would be PAM based. The user/customer creation process would create a new schema in the single database with the username for web/shell/ftp/etc logins. Postgresql can also use PAM for logins and this would allow logins to the database from outside your web app. The web app would always connect to the database as a user with access to all schemas, but would look at the session authentication information to change the active search path to be [username],public. In the case of shell (psql) logins, the default search path would be $user,public as the docs show. If the schemas are created with an AUTORIZATION of the [username] then local (psql) logins would only allow them to see thier schema. But, because the web app is connecting as a user with privileges that allow it to see (the tables in) all schemas, it can act as any user by changing its search_path on a connection by connection basis. greetings Marek greeings Marek ---(end of
[ADMIN] ? in explain query
Hi ,
In the explain below are the references
"outer"."?column2?" = "inner"."?column2?"
Ok?
rt3=# SELECT version();
version
PostgreSQL 7.4beta5 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
rt3=# explain analyze SELECT count(*) from Tickets main JOIN Groups as
Groups_1 ON ( main.id = Groups_1.Instance);
QUERY
PLAN
Aggregate (cost=9047.54..9047.54 rows=1 width=0) (actual
time=2210.017..2210.018 rows=1 loops=1)
- Merge Join (cost=7838.44..8914.51 rows=53211 width=0)
(actual time=1480.912..2111.089 rows=47152 loops=1)
Merge Cond: ("outer"."?column2?"
= "inner"."?column2?")
- Sort (cost=1057.51..1083.58 rows=10431 width=4)
(actual time=124.539..137.337 rows=10431 loops=1)
Sort Key: (main.id)::text
- Seq Scan on tickets main (cost=0.00..361.31
rows=10431 width=4) (actual time=0.032..34.973 rows=10431 loops=1)
- Sort (cost=6780.93..6936.18 rows=62097 width=8)
(actual time=1356.213..1483.773 rows=62097 loops=1)
Sort Key: (groups_1.instance)::text
- Seq Scan on groups groups_1 (cost=0.00..1336.97
rows=62097 width=8) (actual time=0.017..170.204 rows=62097 loops=1)
Total runtime: 2216.263 ms
(10 rows)
rt3=#
Re: [ADMIN] dumping tables from badly damaged db
Brian Ristuccia [EMAIL PROTECTED] writes: Recently I had a problem where a system crash scribbed on some directories, which landed a bunch of files, including a few of the system table files for one of my databases, in lost+found along with a zillion other files. Ugh. I might be able to find the file for this table/index in lost+found, but how do I know what name to give it in /var/lib/postgres/...? I can't think of any reasonably simple way to identify the files by content (this might be something to try to fix in future, but for now you're stuck). Best idea I can think of is to examine od -c dumps and try to intuit which file is which. Currently, I can't connect to the database with the following error: psql: FATAL 1: cannot open pg_class_relname_index: No such file or directory You might be able to get past this by starting a standalone postgres with the -P command-line option (ignore system indexes). If so, try select relname, relfilenode from pg_class. With luck that will give you a list of which file name is needed for each table. I'd not counsel trying to do more than that in the standalone backend until you've gotten at least the tables put back together. You do not need to try very hard to recreate the indexes --- you can use REINDEX to rebuild them. Good luck! regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] SELECT COUNT(*)... returns 0 ROWS
I have instaled Postgres 7.3.4 on RH 9, if I excecute: select count(*) from cj_tranh; count --- 0 (1 row) Why the result us CERO? the table have 1.400.000 rows! What is wrong? Anybody help please. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] Column References
I've got a question about references, is it possible to specify criteria for the Reference. For example, Table 1 has Field1 That References Field1 in Table2, However I only wantField 1 in Table 1to reference the Records in Table2 that have the Valid Field set to TRUE. In other words, I don't want the user to be able to enter invalid records from table 2 into field 1 in table 1. Is there a different way of accomplishing the same thing?
Re: [ADMIN] dumping tables from badly damaged db
Brian Ristuccia [EMAIL PROTECTED] writes: The standalone backend errors out with: FATAL 1: _mdfd_getrelnfd: cannot open relation pg_trigger: No such file or directory Well, if you can identify which of the lost+found files is pg_trigger, you can move it back into place and then try again. (Look for trigger names in the od -c dump...) All the system tables have fixed names (relfilenode values) which you can determine by consulting another database of the same PG version. pg_trigger is 16412 in 7.3, for instance. Lather, rinse, repeat until it comes up ... My week-old backups are starting to look more and more attractive. I didn't say this was going to be painless. regards, tom lane ---(end of broadcast)--- TIP 3: 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] SELECT COUNT(*)... returns 0 ROWS
On Fri, 31 Oct 2003 13:33:09 -0600 PostgreSQL [EMAIL PROTECTED] wrote: I have instaled Postgres 7.3.4 on RH 9, if I excecute: select count(*) from cj_tranh; count --- 0 (1 row) Why the result us CERO? the table have 1.400.000 rows! What is wrong? Anybody help please. 1. did you remember to load data? 2. did someone accidentally delete the data? 3. are you connected to the correct db (I've panic'd before but realized I was on dev, not production!)? 4. sure that is the right table? -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] dumping tables from badly damaged db
On Fri, Oct 31, 2003 at 02:05:57PM -0500, Tom Lane wrote: You might be able to get past this by starting a standalone postgres with the -P command-line option (ignore system indexes). If so, try select relname, relfilenode from pg_class. With luck that will give you a list of which file name is needed for each table. I'd not counsel trying to do more than that in the standalone backend until you've gotten at least the tables put back together. The standalone backend errors out with: FATAL 1: _mdfd_getrelnfd: cannot open relation pg_trigger: No such file or directory I suspect my troubles may be more severe than just a missing index... My week-old backups are starting to look more and more attractive. Any other hints? Thanks. -Brian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] ? in explain query
Rajesh Kumar Mallah [EMAIL PROTECTED] writes: In the explain below are the references outer.?column2? = inner.?column2? Ok? Yeah, those are variables that don't have any name because they don't correspond exactly to table columns. It looks like the plan is merge-joining (main.id)::text to (groups_1.instance)::text. At the level of the scans it's possible to see the expressions involved, but at the level of the join those are just Var references to the outputs of the lower plan steps. We could possibly alter EXPLAIN to print the referred-to expression instead of faking up a name for the Var node, but this would make the printout look like the expression was being recomputed at the upper level, which it isn't. regards, tom lane ---(end of broadcast)--- TIP 3: 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] fishing out LOG: duration lines from the logfile.
Rajesh Kumar Mallah wrote:
Hi,
I think its not going to a trivial task to take out only the LOG duration
lines from a PostgreSQL logfile. We need to extract the duration and
the actual statement. I think i will put a custom delimeters around the
statements for the time being so that the log parser can parse it
unambigiously. Is there any better method?
Seeing that I was involved in implementing this behavior, I felt I
should write a script to pull out this information to see how hard it
would be. Here is an awk script:
awk ' BEGIN {in_statement = N}
{
while (getline 0)
{
if ($1 == LOG: $2 == duration: $5 ==
statement:)
{
print $0;
in_statement = Y;
}
else if (in_statement == Y $0 ~ /^ /) # -- tab
{
print $0;
}
else
in_statement = N;
}
}' $@
I tested this with the log file you included and it seeme to work fine,
though the email had some line wraps I had to remove.
--
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]
Re: [linux-lvm] RE: [ADMIN] [PERFORM] backup/restore - another ar ea.
On Thu, Oct 30, 2003 at 10:28:10AM -0700, [EMAIL PROTECTED] wrote: Does xfs_freeze work on red hat 7.3? It works on any kernel with XFS (it talks directly to XFS). cheers. -- Nathan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [linux-lvm] RE: [ADMIN] [PERFORM] backup/restore - another
Does xfs_freeze work on red hat 7.3? Cynthia Leon -Original Message- From: Murthy Kambhampaty [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2003 11:34 AM To: 'Tom Lane'; Murthy Kambhampaty Cc: 'Jeff'; Josh Berkus; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [linux-lvm] RE: [ADMIN] [PERFORM] backup/restore - another area. Friday, October 17, 2003 12:05, Tom Lane [mailto:[EMAIL PROTECTED] wrote: Murthy Kambhampaty [EMAIL PROTECTED] writes: ... The script handles situations where (i) the XFS filesystem containing $PGDATA has an external log and (ii) the postmaster log ($PGDATA/pg_xlog) is written to a filesystem different than the one containing the $PGDATA folder. It does? How exactly can you ensure snapshot consistency between data files and XLOG if they are on different filesystem Say, you're setup looks something like this: mount -t xfs /dev/VG1/LV_data /home/pgdata mount -t xfs /dev/VG1/LV_xlog /home/pgdata/pg_xlog When you want to take the filesystem backup, you do: Step 1: xfs_freeze -f /dev/VG1/LV_xlog xfs_freeze -f /dev/VG1/LV_data This should finish any checkpoints that were in progress, and not start any new ones till you unfreeze. (writes to an xfs_frozen filesystem wait for the xfs_freeze -u, but reads proceed; see text from xfs_freeze manpage in postcript below.) Step2: create snapshots of /dev/VG1/LV_xlog and /dev/VG1/LV_xlog Step 3: xfs_freeze -u /dev/VG1/LV_data xfs_freeze -u /dev/VG1/LV_xlog Unfreezing in this order should assure that checkpoints resume where they left off, then log writes commence. Step4: mount the snapshots taken in Step2 somewhere; e.g. /mnt/snap_data and /mnt/snap_xlog. Copy (or rsync or whatever) /mnt/snap_data to /mnt/pgbackup/ and /mnt/snap_xlog to /mnt/pgbackup/pg_xlog. Upon completion, /mnt/pgbackup/ looks to the postmaster like /home/pgdata would if the server had crashed at the moment that Step1 was initiated. As I understand it, during recovery (startup) the postmaster will roll the database forward to this point, checkpoint-ing all the transactions that made it into the log before the crash. Step5: remove the snapshots created in Step2. The key is (i) xfs_freeze allows you to quiesce any filesystem at any point in time and, if I'm not mistaken, the order (LIFO) in which you freeze and unfreeze the two filesystems: freeze $PGDATA/pg_xlog then $PGDATA; unfreeze $PGDATA then $PGDATA/pg_xlog. (ii) WAL recovery assures consistency after a (file)sytem crash. Presently, the test server for my backup scripts is set-up this way, and the backup works flawlessly, AFAICT. (Note that the backup script starts a postmaster on the filesystem copy each time, so you get early warning of problems. Moreover the data in the production and backup copies are tested and found to be identical. Comments? Any suggestions for additional tests? Thanks, Murthy PS: From the xfs_freeze manpage: xfs_freeze suspends and resumes access to an XFS filesystem (see xfs(5)). xfs_freeze halts new access to the filesystem and creates a stable image on disk. xfs_freeze is intended to be used with volume managers and hardware RAID devices that support the creation of snapshots. The mount-point argument is the pathname of the directory where the filesystem is mounted. The filesystem must be mounted to be frozen (see mount(8)). The -f flag requests the specified XFS filesystem to be frozen from new modifications. When this is selected, all ongoing transactions in the filesystem are allowed to complete, new write system calls are halted, other calls which modify the filesystem are halted, and all dirty data, metadata, and log information are written to disk. Any process attempting to write to the frozen filesystem will block waiting for the filesystem to be unfrozen. Note that even after freezing, the on-disk filesystem can contain information on files that are still in the process of unlinking. These files will not be unlinked until the filesystem is unfrozen or a clean mount of the snapshot is complete. The -u option is used to un-freeze the filesystem and allow operations to continue. Any filesystem modifications that were blocked by the freeze are unblocked and allowed to complete. ___ linux-lvm mailing list [EMAIL PROTECTED] http://lists.sistina.com/mailman/listinfo/linux-lvm read the LVM HOW-TO at http://tldp.org/HOWTO/LVM-HOWTO/ == --- PRESBYTERIAN HEALTHCARE SERVICES DISCLAIMER --- This message originates from Presbyterian Healthcare Services or one of its affiliated organizations. It contains information, which may be confidential or privileged, and is intended only for the individual or entity named above. It is prohibited for anyone else to disclose, copy, distribute or use the contents of this message. All personal messages express views
Re: [ADMIN] Table versions
What I did next, is put a trigger on pg_attribute that should, in theory, on insert and update, fire up a function that will increment a version System tables do not use the same process for row insertion / updates as the rest of the system. You're trigger will rarely be fired. signature.asc Description: This is a digitally signed message part
Re: [ADMIN] [SQL] Table versions
Tom Lane [EMAIL PROTECTED] writes: Returning to the original problem, it seems to me that comparing pg_dump -s output is a reasonable way to proceed. I've actually started checking in a pg_dump -s output file into my CVS tree. However I prune a few key lines from it. I prune the TOC OID numbers from it, and anything not owned by the user I'm interested in. The makefile rule I use looks like: schema.sql: pg_dump -U postgres -s user | sed '/^-- TOC entry/d;/^\\connect - postgres/,/^\\connect - user/d;/^SET search_path/d;/^$$/d;/^--$$/d' $@ This still suffers from one major deficiency. The order that objects are outputed isn't necessarily consistent between databases. If I add tables to the development server but then add them to the production server in a different order the schema still shows differences even though the objects in the two databases are identical. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
