[ADMIN] Recovery/Rollback question
Hi, I am new to the list and want to say hello first. We are migrating to Postgres and therefore my question might be simple for you. We run several application databases under one db server. Now we are looking for a mechanism to rollback unwanted user command without impact for other databases. Is there a posiibilty to use something like WAL on tablespaces or databases not only on db servers? Does Postgres provide other utilities for this? Which strategies to you use? Scenatio running databases a and b under the server. It is now 10 o 'clock, user made a mistake at 8 o'clock in database a, so we need to roll database a back to 8 o'clock, database b keeps current data status. How do you solve this under Postgres? Any links to useful docu chapters ? Regards, A.Stejskal ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] can i use to_ascii function ?
[Once again, please copy the mailing list on replies so others can participate in and learn from the discussion. Also, pgsql-general might be a more appropriate list than pgsql-admin.] On Mon, Feb 26, 2007 at 10:53:48AM +0100, j n wrote: 1. At first i tried use to_ascii ... convert it works well on some letter 'á' converted to 'a' 'é' to 'e' but some of them like č or š convert as empty string it means 'not working fine' We can't explain why this doesn't work unless you show exactly what you did. Please post a set of SQL statements that somebody could run in their own database to reproduce the problem you're seeing. Did any of this data originate on Windows? If so then it's possible that some accented characters aren't represented by the proper Unicode code points. This can happen, for example, if you load Windows-1250 data into the database with client_encoding set to LATIN2. Depending on how you're viewing the data the wrong characters might still display correctly. To give a specific example, š is 0x9a in Windows-1250 but if you load this character with client_encoding set to LATIN2 then the database converts it to U+009A, a control character, instead of to U+0161 latin small letter s with caron (háček). An application that reads the control character might render it as š assuming that that's what character was meant, but functions that operate on the data won't work as expected. This wouldn't fully explain the problems you're seeing but it's something I've seen cause similar problems. 2. than i tried to use perl func but i didn't have configured postgres to use perl so i have to : ./configure --enable-multibyte=UTF8 prefix=/usr/local/pgsqlProd --exec-prefix=/usr/local/pgsqlProd --with-perl The --enable-multibyte option was removed in 7.3 so you don't need it; at the end of the configure you should have seen a warning that this option was ignored. And did you mean --prefix instead of prefix? Also, there's no need to set --exec-prefix if it gets the same value as --prefix. gmake gmake install 3. After this everythink have started to work fine also unaccent perl function ... Configuring with --with-perl is necessary if you want to use server- side Perl functions, but you didn't say anything about Perl functions in your previous message. You said that to_ascii() and convert() didn't work unless you used --with-perl, which doesn't make sense because those functions have nothing to do with Perl. I tried it both ways and got the same behavior so I'm still skeptical that --with-perl is the relevant difference for to_ascii() and convert() behavior. -- Michael Fuhr ---(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
[ADMIN] host name?
Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor
Re: [ADMIN] host name?
Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place?
Re: [ADMIN] host name?
And one more question. Is there a way (function/view) to find machine name on which user program (connected to Postgres) runs? Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Neyman Sent: Monday, February 26, 2007 10:29 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] host name? Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor
[ADMIN] kerberized odbc driver
I was hoping there was a kerberized odbc driver for postgresql that works on windows. I know this is a longshot. Anyone? -- David Bear phone: 602-496-0424 fax:602-496-0955 College of Public Programs/ASU University Center Rm 622 411 N Central Phoenix, AZ 85007-0685 Beware the IP portfolio, everyone will be suspect of trespassing ---(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] host name?
You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place?
[ADMIN] UNSUBSCRIBE
UNSUBSCRIBE - Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). Probalo ya!
Re: [ADMIN] kerberized odbc driver
David Bear wrote: I was hoping there was a kerberized odbc driver for postgresql that works on windows. I know this is a longshot. Anyone? You are shooting over a mountain with a plastic arrow. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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
[ADMIN] Disconnecting non active (IDLE ) users
I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from desktop applications and 200 users connecting thru web service from handheld computers I have problem with second groups of users. Often they do not disconnect from POSTGRE Server and with time passing thru I have lot of IDLE users and very much memory consumptions. From time to time that can crash server then only restarting server will kill all postgres.exe from memory and this annoying me, because that I build script to restart server every night. I could not get programmers to change program. Is there parameters which will disconnect IDLE users if they excided some time or some program which will purge memory from non active posgres.exe Thanks in advance [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] kerberized odbc driver
Mon, Feb 26, 2007 at 09:06:04AM -0800, Joshua D. Drake napsal(a): David Bear wrote: I was hoping there was a kerberized odbc driver for postgresql that works on windows. I know this is a longshot. Anyone? I think it works with psqlodbc. Isn't it? BTW at least 08.01.X series works. You have to specify some odd info. But there is a way. Let's look at pgfoundry bugs (maybe closed) from Magnus in the end of 2005 or in year 2006. The libpq supports it and 08.01.X series was based on libpq. 08.02 use libpq for SSL and krb (I think) too. Where does you expectation differ from reality? Regards, Luf ---(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
[ADMIN] U NSUBSCRIBE
---(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] kerberized odbc driver
David Bear [EMAIL PROTECTED] writes: I was hoping there was a kerberized odbc driver for postgresql that works on windows. I know this is a longshot. I'm afraid so ... but Those Who Would Know are much more likely to be hanging out in pgsql-odbc than on this list. regards, tom lane ---(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] host name?
not sure how to resolve the name, but maybe this is close enough: select usename, client_addr from pg_catalog.pg_stat_activity; On Mon, Feb 26, 2007 at 11:47:13AM -0500, Igor Neyman wrote: You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place? -- You have no chance to survive make your time. ---(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] host name?
Right, I looked at pg_stat_activity, but besides having ip address (instead of machine name), it doesn't have clent program name connected to PG. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Stell Sent: Monday, February 26, 2007 12:53 PM To: Igor Neyman Cc: Andy Shellam (Mailing Lists); pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? not sure how to resolve the name, but maybe this is close enough: select usename, client_addr from pg_catalog.pg_stat_activity; On Mon, Feb 26, 2007 at 11:47:13AM -0500, Igor Neyman wrote: You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place? -- You have no chance to survive make your time. ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] host name?
Right, I looked at pg_stat_activity. But besides having ip address (instead of machine name), it doesn't have clent program name connected to PG. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ray Stell Sent: Monday, February 26, 2007 12:53 PM To: Igor Neyman Cc: Andy Shellam (Mailing Lists); pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? not sure how to resolve the name, but maybe this is close enough: select usename, client_addr from pg_catalog.pg_stat_activity; On Mon, Feb 26, 2007 at 11:47:13AM -0500, Igor Neyman wrote: You are right, wrong question. What I really want to know is formulated in my next message. Which is: based on program (connected to PG) name find the machine name it runs on. For those familiar with Oracle, it's program, machine columns in v$session view. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andy Shellam (Mailing Lists) Sent: Monday, February 26, 2007 10:56 AM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] host name? Igor Neyman wrote: Is there a function (or catalog view) that I can call in psql and that will tell me host name of the machine on which Postgres database is running? Igor Surely you need to know this to connect to it in the first place? -- You have no chance to survive make your time. ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] host name?
Igor Neyman wrote: But besides having ip address (instead of machine name), it doesn't have clent program name connected to PG. That information is not available to the server. -- 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] Recovery/Rollback question
This is currently unsupported in mainstream PostgreSQL, as the WAL only works on the cluster basis. The only options you currently have for performing this is to use the last full backup and copies of WAL files to restore the cluster on a second server to your specific point in time, and restore that copy of the database in question to your primary server. Jason Minion [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, February 26, 2007 7:57 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Recovery/Rollback question Hi, I am new to the list and want to say hello first. We are migrating to Postgres and therefore my question might be simple for you. We run several application databases under one db server. Now we are looking for a mechanism to rollback unwanted user command without impact for other databases. Is there a posiibilty to use something like WAL on tablespaces or databases not only on db servers? Does Postgres provide other utilities for this? Which strategies to you use? Scenatio running databases a and b under the server. It is now 10 o 'clock, user made a mistake at 8 o'clock in database a, so we need to roll database a back to 8 o'clock, database b keeps current data status. How do you solve this under Postgres? Any links to useful docu chapters ? Regards, A.Stejskal ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] Recovery/Rollback question
On Mon, 2007-02-26 at 07:57, [EMAIL PROTECTED] wrote: Hi, I am new to the list and want to say hello first. We are migrating to Postgres and therefore my question might be simple for you. We run several application databases under one db server. Now we are looking for a mechanism to rollback unwanted user command without impact for other databases. Is there a posiibilty to use something like WAL on tablespaces or databases not only on db servers? Does Postgres provide other utilities for this? Which strategies to you use? Scenatio running databases a and b under the server. It is now 10 o 'clock, user made a mistake at 8 o'clock in database a, so we need to roll database a back to 8 o'clock, database b keeps current data status. How do you solve this under Postgres? Any links to useful docu chapters ? You can solve it with PITR (point in time recovery) but note that it will roll back the whole cluster, not just one db in it. However, PITR is generally run on a secondary server, so that's ok. If you're not set up for PITR, you've asked a little late (i.e. after the fact is too late) as it needs to be setup ahead of time normally. You might want to set up your machine to do PITR, but you need an image of your database files from before the pebcak* incident. Your users need to learn how to use transactions. You can run some pretty big transactions and roll them back if you don't like the changes. Once you commit though, you can't roll it back without using disaster recovery techniques like PITR. Look up PITR in the docs, it's a useful piece of kit. *pebcak: Problem exist(s|ed) between chair and keyboard. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
