[ADMIN] pg_dump issue across different versions (7.1.2 & 7.2).
hi, here i have 2 machines which have postgres 7.1.2 & 7.2 running, respectively. I did try pg_dump of database in machine (7.2) from the machine where i have 7.1.2 and it produced the message Archiver(db) version: 7.1 Aborting because of version mismatch. Use --ignore-version if you think it's safe to proceed anyway. so i did try with the option '--ignore-version', then it produced Archiver(db) version: 7.1 Proceeding despite version mismatch. getTables(): SELECT (for PRIMARY KEY) failed on table address. Explanation from backend: ERROR: Attribute 'oid' not found --- what seems to be the problem here?? how could i overcome this problem? one way is to dump in corresponding machine. any other way? any valuable suggestions are very welcome at the earliest, please! Regards, Bhuvaneswaran. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Help me!
On Apr 16, Dieudonne Nanga wrote:
> How do i insert a file in the database?
You mean large objects?
If yes, try
# select lo_import('');
this will return a OID.
and to access the same, try
# select lo_export(, '');
>
> ---(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
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Mirroring ENTIRE DATABASE.
Hi, How can we mirror an ENTIRE DATABASE? I have came across pgreplicator and it seems that it will mirror ONLY TABLES. Then how about views, functions, triggers, sequences, etc.(somebody correct me if wrong) So am i unable to mirror a ENTIRE DATABASE in postgres? Else shall i do this way on per day basis. $ pg_dump -h localhost dbname user | psql -h hostname dbname user Or is there any other better option than this one. Ofcourse i am planning to mirror within LAN. Expecting your valuable suggestions. regards, bhuvaneswaran. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Mirroring ENTIRE DATABASE.
Dear Rajesh Kumar Mallah, Thankx for your response and sorry for my late response. Here find the link for pgreplicator user discussion. http://sourceforge.net/forum/forum.php?forum_id=60248 I wasn't aware of usogres & rserv forums and would be better if someone point those links. regards, bhuvaneswaran. On May 30, Rajesh Kumar Mallah. wrote: > Dear Bhuvan, > > Advanceded replication features although important > are not currently available with postgresql. > > > Some solutions do exists like pgreplicator, usogres > rserv etc, but not sure of their status. > > Also i would like to ask the list if there exists > some place where these are discussed,.. > > > regds > Mallah. > > > On Wednesday 29 May 2002 12:54 pm, Bhuvan A wrote: > > Hi, > > > > How can we mirror an ENTIRE DATABASE? I have came across pgreplicator > > and it seems that it will mirror ONLY TABLES. Then how about views, > > functions, triggers, sequences, etc.(somebody correct me if wrong) > > > > So am i unable to mirror a ENTIRE DATABASE in postgres? Else shall i > > do this way on per day basis. > > > > $ pg_dump -h localhost dbname user | psql -h hostname dbname user > > > > Or is there any other better option than this one. Ofcourse i am > > planning to mirror within LAN. Expecting your valuable suggestions. > > > > regards, > > bhuvaneswaran. > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > ---(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] createuser --password
Hi, I am not sure about the command line option, but the password for any user can be set using ALTER USER sql command. ALTER USER my_user ENCRYPTED PASSWORD 'my_pass'; also refer the documentation for details. regards, bhuvaneswaran. On 8 Jul 2002, Stephane Schildknecht wrote: > Hi, > > Is there a way to give the password of the user to be created on the > command line ? > > S@S > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] function in place of sub-select
> One way might be to create a C (or C++) program that emulates your my_func > function. > > We've tried doing this and did some benchmarks that show that the system > works quite faster when you take out the stored procedures/functions and > "translate" them into C programs. Rightly said. C functions work faster than the stored procedures. But C function too wont fit my case. The interpretation, to feed data to the IN operator cannot be performed properly. How else do we achieve this? regards, bhuvaneswaran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] function in place of sub-select
Hi, I am using postgresql 7.2 on RHL7.3. I know that we cannot return a record or multiple values from a plpgsql procedure unless otherwise it is a triggered procedure. But i have a need to use a function in place of sub-select, something like select * from my_table where field1 in (select my_func()); my_func() returns a value of type text. I am literally parsing the values in my_func() to return the value, in the format acceptable by IN operator. Something like, "'value1', 'value2'". But it has been interpreted as it is instead of 'value1' and 'value2' seperately and resulting in 0 record. Again, a view instead of function in a sub-select donot help in my case, since i have lot of processing involved in the function. How do i accompanise it in function? Or whats the other way? TIA. regards, bhuvaneswaran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] pg_log: no such file or directory
Hi, I use postgresql 7.2. Last night i received a strange error and the postmaster was crashed. It reported 'cannot read block 1470 of pg_log: no such file or directory'. While googling i found, "You can change "/\name/" to "/name/" in src/backend/catalog/genbki.sh, then re-make install in that directory; or just use the latest nightly snapshot." But in script src/backend/catalog/genbki.sh, i donot find any string "/\name/". Rather i find "/ name/" at one instance. Do i have to make my change here and re-make? Or i have to upgrade to latest version? In which case i can able to recover my data? How do i recover my data then? Valuable suggestions are very welcome. TIA. regards, bhuvaneswaran ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] pg_dump question
> I need to set up a daily cron job that performs a pg_dump on a remote > database. I can't figure out how to pass a password to it. How do I do > this. Everything I've tried still comes up with asking me for a password > when I test it. > $ export PGPASSWORD=user_pass $ echo -e "Would do the trick. Yes, it will not ask for the password again"; regards, bhuvaneswaran ---(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] pg_log: no such file or directory
> > I use postgresql 7.2. Last night i received a strange error and the > > postmaster was crashed. It reported > > 'cannot read block 1470 of pg_log: no such file or directory'. > > There is no pg_log in 7.2. You'll need to be more accurate about your > situation if you want help ... > Sorry for the inconvenience. $ cat PG_VERSION 7.0 It had been recovered. I did initdb and restored the backup i had. Is there any other way to recover, in absence of backup? Please let us know. For your reference, here is the log: database system was interrupted being in recovery at 2002-10-28 16:34:50 IST This propably means that some data blocks are corrupted and you will have to use last backup for recovery. DEBUG: CheckPoint record at (2, 1439679740) DEBUG: Redo record at (2, 1439679740); Undo record at (0, 0); Shutdown FALSE DEBUG: NextTransactionId: 48106942; NextOid: 3022880 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (2, 1439679804) FATAL 2: cannot read block 1470 of pg_log: No such file or directory /usr/local/pgsql/bin/postmaster: Startup proc 11284 exited with status 512 - abo regards, bhuvaneswaran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] loading and unloading rows
> So, I ran psql, did a \o to capture the output, > did a select * from table where x=y > whacked the first row in the output file, and added the COPY statement. test_db=> \pset tuples_only -- does whacking for you > This seems way too klunky, so I must be going about it all wrong. In > Informix-land I would just do a unload then a load. What is the postgresql > equivalent? It is possible. Delimiters can be used while restoring the data file. Null string specification can also be specified. See, test_db=> \h COPY for more details. In addition you should also do some find and replace in the data file before restoring it to another database. They are, s/\s*|\s*//g s/^\s*//g Now, i bet you can able to restore. regards, bhuvaneswaran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Disabling triggers on a relation.
> What is the correct method of temporarily > disabling all triggers from a table? Disable: db=# update pg_class set reltriggers = '0' where relname = 'your_table'; Enable: db=# update pg_class set reltriggers = (select count(*) from pg_trigger where pg_class.oid = tgrelid) where relname = 'your_table'; > > > also is there any command/function to show the > trigger defination ? like pg_index_def does for indexes? > Not sure. regards, bhuvaneswaran ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] psql to 7.2.3 from 7.3.1
Hi, I have pg7.3.1 in my PC and pg7.2.3 in another machine. While i tried psql pg7.2.3 from pg7.3.1 machine, it reports ERROR: parser: parse error at or near "." But still, it established the connection. Then it was found that i was unable to execute any of the forward slash (\) commands from that version of psql. It reports similar error for every forward slash (\) commands. Does it due to the fact that psql of 7.3.1 has more features than 7.2.3 does? How can i overcome this problem? Does using 7.2.3 or 7.3.1 on both machines the only way? Sample test: $ psql -V psql (PostgreSQL) 7.3.1 contains support for command-line editing [bhuvan@Bhuvan bhuvan]$ psql -hdbserver test_db test_db Password: ERROR: parser: parse error at or near "." Welcome to psql 7.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DES-CBC3-SHA, bits: 168) test_db=> \dt ERROR: parser: parse error at or near "." test_db=> SELECT version(); version - PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) test_db=> regards, bhuvaneswaran ---(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] deadlock problem in Ad serving..
> ~~ > Error: DBD::Pg::st execute failed: ERROR: deadlock detected at > /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. > ~~ It is a genuine error, occurs while two or more transaction process tries to update/delete a same record simultaneously. You can overcome this by locking the table in share row exclusive mode also. More details can be found in the lock command documentation. regards, bhuvaneswaran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
