[ADMIN] Querying
Is it possible to query another database while in 'psql' with another database? - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Re: [ADMIN] Querying
DJ Pichay написа: > Is it possible to query another database while in 'psql' with another > database? http://www.postgresql.org/docs/faqs.FAQ.html#item4.17 -- Milen A. Radev ---(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] Dump database more than 1 flat file
Hi, On Dienstag, 18. Dezember 2007, Alvaro Herrera wrote: | A.Burbello wrote: | > If doesn't have another way, how can I put the | > "header" in the begin of file without open? | > With "cat >>" command I put in the end. | | Don't -- you can put the header in a separate file and do something like | | (cat header-file ; cat split-1 ; cat tail-file ) | psql even simpler: "cat" usually takes any number of file name arguments, so usually you simply can do cat header-file split-1 tail-file | psql No need for parentheses and starting a new process for each file. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] PITR and modification of the backup database
Hi, we are running a PostgreSQL server (8.2.4) which is used for our internet service and use log file shipping to get a near-real-time backup. As the hardware for the backup database server is quite expensive we would like to use it for internal reporting purposes. That's why we have done the following: Server A is the production server, Server B is the backup server. The database that should be backed up (from A) is called prod_db. We created another (empty) database reporting_db on the _production_ server. Once a day we create a base backup and copy it to the backup server. Then we startup the server and replay all WAL files that are available. Then we create the records in the reporting_db on the backup server that we need for our reporting purposes. In case of a fatal hardware failure of the production server we would like to switch as fast as possible to the backup server. All new WAL files should be replayed and then the backup server should be used as production server. The usage for reporting purposes would have to be stopped until the other server is repaired. My question is: do we get in trouble when we change the contents of a database on the backup server and then try to replay the latest log files from the production server? The database on the backup server which we change is not used on the production server and it's contents don't have to be preserved. We are only interested in the contents of the production database prod_db (which provides the basis for the reporting database). Best regards Soeren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Warm-standby robustness question
Hi, We have two PostgreSQL 8.2 database servers: A master and a warm-standby server. We plan on making an initial backup of the master onto the standby and then use log-shipping with "real-time" WAL-file processing as described in http://www.postgresql.org/docs/8.2/static/warm-standby.html My question is this: If the master database is fairly busy, gets VACUUMed once a day, etc. can we expect the warm standby server to work correctly after days/weeks/months/years of log shipping, or should we periodically take new base backups? How long would you go between base backups? Or is one initial copy really sufficient with WAL-shipping-and-consuming working perfectly thereafter? Regards, David. ---(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
Re: [ADMIN] Warm-standby robustness question
"David F. Skoll" <[EMAIL PROTECTED]> writes: > My question is this: If the master database is fairly busy, gets > VACUUMed once a day, etc. can we expect the warm standby server > to work correctly after days/weeks/months/years of log shipping, > or should we periodically take new base backups? I don't think the time period is at issue. Log-shipping should keep the slave a perfect replica of the master (if it doesn't, we have problems anyway). The operational question you need to ask yourself is: if you haven't swapped to the slave lately, how do you know it will work when you need it to? The current backup/restore docs suggest as best practice that you intentionally swap master and slave periodically, ie, fail over to the slave and then re-initialize the master as a new slave. This provides a periodic test that your fail-over mechanisms actually work, and as a bonus gives you a chance for a maintenance window on the ex-master before it's brought up as new slave. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] Set maintenance work mem for pg_restore
I see a lot of suggestions to increase maintenance work mem if running pg_restore. This is to help with the index creation and initial sort from what I understand. A few tests I ran does show this helps. Does anyone know if there is a way to only "temporarily" set this setting? I have some DBs that are bloated and I have a script to run off hours (I don't like working late). This uses pg_dump and pg_restore to recover the disk space. I found that this is usually faster than 'vacuum full', and is useful in some cases. I know I can set this for my current session dynamically "set maintenance_work_mem = x", but it seems that the only way I can do this for pg_restore is to update the configuration, reload and then change it back when the script completes and reload again. Thanks Deron
Re: [ADMIN] Set maintenance work mem for pg_restore
Deron escribió: > I see a lot of suggestions to increase maintenance work mem if running > pg_restore. This is to help with the index creation and initial sort from > what I understand. A few tests I ran does show this helps. > Does anyone know if there is a way to only "temporarily" set this setting? > I have some DBs that are bloated and I have a script to run off hours (I > don't like working late). This uses pg_dump and pg_restore to recover the > disk space. I found that this is usually faster than 'vacuum full', and is > useful in some cases. You can specify it via PGOPTIONS: $ PGOPTIONS='-c maintenance_work_mem=1GB' psql Welcome to psql 8.2.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit alvherre=# show maintenance_work_mem ; maintenance_work_mem -- 1GB (1 row) alvherre=# \q $ psql Welcome to psql 8.2.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit alvherre=# show maintenance_work_mem ; maintenance_work_mem -- 16MB (1 row) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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
Re: [ADMIN] Set maintenance work mem for pg_restore
On Dec 18, 2007 2:30 PM, Deron <[EMAIL PROTECTED]> wrote: > I see a lot of suggestions to increase maintenance work mem if running > pg_restore. This is to help with the index creation and initial sort from > what I understand. A few tests I ran does show this helps. > Does anyone know if there is a way to only "temporarily" set this setting? > I have some DBs that are bloated and I have a script to run off hours (I > don't like working late). This uses pg_dump and pg_restore to recover the > disk space. I found that this is usually faster than 'vacuum full', and is > useful in some cases. > > I know I can set this for my current session dynamically "set > maintenance_work_mem = x", but it seems that the only way I can do this > for pg_restore is to update the configuration, reload and then change it > back when the script completes and reload again. If you use psql to restore, you can just add the set maintenance_work_mem = xxx at the top of the file. Also you can do it by creating a "special" superuser and altering that user to inherit this new setting: create user bubba superuser; alter user bubba set maintenance_work_mem TO 512000; then just connect as that user to run your restores or what not. You can also set it for a non-super user, or a database as well. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Set maintenance work mem for pg_restore
Deron <[EMAIL PROTECTED]> writes: > Does anyone know if there is a way to only "temporarily" set this setting? Aside from the specific answers already given, the general answer is to read http://www.postgresql.org/docs/8.2/static/config-setting.html which enumerates all (most?) of the many ways you can set configuration parameters. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] How to uses self query plan
Hello, My purpose is to uses different query plan depending on queries I got the following problem : some queries are taking too much time and postgresql does not take the most relevant index in account. I search on internet and found that no HINT function was available in postgresl. I tried to change indexes parameters in postgresql.conf but eache time I set a param to OFF (example nested_loop ,seqscan, ...) , some queries freeze database . How can we force postgresql to use a plan ? olivier ---(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] Set maintenance work mem for pg_restore
Thanks for this and the other suggestions. This is exactly what I was looking for. Deron On 12/18/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > Deron escribió: > > I see a lot of suggestions to increase maintenance work mem if running > > pg_restore. This is to help with the index creation and initial sort > from > > what I understand. A few tests I ran does show this helps. > > Does anyone know if there is a way to only "temporarily" set this > setting? > > I have some DBs that are bloated and I have a script to run off hours (I > > don't like working late). This uses pg_dump and pg_restore to recover > the > > disk space. I found that this is usually faster than 'vacuum full', and > is > > useful in some cases. > > You can specify it via PGOPTIONS: > > $ PGOPTIONS='-c maintenance_work_mem=1GB' psql > Welcome to psql 8.2.5, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > alvherre=# show maintenance_work_mem ; > maintenance_work_mem > -- > 1GB > (1 row) > > alvherre=# \q > > $ psql > Welcome to psql 8.2.5, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > alvherre=# show maintenance_work_mem ; > maintenance_work_mem > -- > 16MB > (1 row) > > > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
Re: [ADMIN] How to uses self query plan
On Dec 18, 2007 4:21 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hello, > > My purpose is to uses different query plan depending on queries > I got the following problem : some queries are taking too much time and > postgresql does not take the most relevant index in account. > I search on internet and found that no HINT function was available in > postgresl. > I tried to change indexes parameters in postgresql.conf but eache time I > set a param to OFF (example nested_loop ,seqscan, ...) , some queries > freeze database . > > How can we force postgresql to use a plan ? That's not how we do things in postgresql land (usually). You should figure out WHY your queries are picking the wrong plan, and then see if you can get them to pick the right ones. If it's a query planner bug, you report it here, or the perform or general lists, and it gets fixed. Generic hints aren't likely to happen any time soon, although I do believe 8.3 is introducing function costing of some kind, which seems like a useful idea. But, back to fixing your slow queries. 1: Increase statistics targets on the guilty columns and reanalyze. 2: Run explain analyze select and post the output here. 3: ??? 4: profit? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] How to uses self query plan
Scott Marlowe a écrit : On Dec 18, 2007 4:21 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hello, My purpose is to uses different query plan depending on queries I got the following problem : some queries are taking too much time and postgresql does not take the most relevant index in account. I search on internet and found that no HINT function was available in postgresl. I tried to change indexes parameters in postgresql.conf but eache time I set a param to OFF (example nested_loop ,seqscan, ...) , some queries freeze database . How can we force postgresql to use a plan ? That's not how we do things in postgresql land (usually). You should figure out WHY your queries are picking the wrong plan, and then see if you can get them to pick the right ones. If it's a query planner bug, you report it here, or the perform or general lists, and it gets fixed. Generic hints aren't likely to happen any time soon, although I do believe 8.3 is introducing function costing of some kind, which seems like a useful idea. But, back to fixing your slow queries. 1: Increase statistics targets on the guilty columns and reanalyze. 2: Run explain analyze select and post the output here. 3: ??? 4: profit? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate Thanks for reply I will extract some queries. I want to precise that I use postgresql 8.1.0 . Olivier begin:vcard fn:Olivier Boissard n:Boissard;Olivier org;quoted-printable:CERENE SERVICES;d=C3=A9veloppement adr:;;3 rue archimede;La Chapelle Saint Luc;AUBE;10600;FRANCE email;internet:[EMAIL PROTECTED] tel;work:+33.3.25.74.11.78 tel;fax:+33.3.25.78.39.67 note;quoted-printable:"Ce message peut contenir des informations confidentielles et/ou=0D=0A= prot=C3=A9g=C3=A9es. Il est =C3=A0 l'usage exclusif de son destinataire.= Toute=0D=0A= utilisation non autoris=C3=A9e peut =C3=AAtre illicite. Si vous recevez= ce=0D=0A= message par erreur, nous vous remercions d'en aviser imm=C3=A9diatement=0D=0A= l'exp=C3=A9diteur en utilisant la fonction r=C3=A9ponse de votre gestionn= aire=0D=0A= de courrier =C3=A9lectronique."=0D=0A= =0D=0A= "This email may contain confidential information and/or copyright=0D=0A= material. This email is intended for the use of the addressee only.=0D=0A= Any unauthorised use may be unlawful. If you receive this email by=0D=0A= mistake, please advise the sender immediately by using the reply=0D=0A= facility in your email software." url:www.cerene.fr version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
