[ADMIN] Sharing Postgres Datafiles
I want to share a Postgres DB between 2 servers, Both these systems will be running Postgres,I need to share the Database files, these files need to be stored on a file server, Do I only copy the /base directory to the file server and mount this directory on /var/lib/pgsql/base. ? Both systems will not be accessing these at the same time, its only if one server goes down the other will take over. Sean
[ADMIN] another pg database corruption?
Hi all, I am using pg 7.3.4 on linuz red hat 7.3 and reiserFS. After ~30 days error free work my pg stops. I my log I found: PANIC: open of /mnt/diske/skladdb/pg_clog/0D02 failed: No such file or directory LOG: statement: COPY public.a_sp (ids, ids_ma, ids_mp, ids_mr, ids_mpr, ids_mpdt, ids_mpkt, smetkas, smetkaa, smetkad1, smetkad2, tip, vaji, ime, imeeng, balans, opr, pp, prihrazh_razh, prihrazh_prih, ids_firma) TO stdout; LOG: server process (pid 1252) was terminated by signal 6 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing shared memory and semaphores LOG: database system was interrupted at 2003-10-27 14:34:01 EET LOG: checkpoint record is at 2/1C591C4C LOG: redo record is at 2/1C591C4C; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 493496; next oid: 197060600 LOG: database system was not properly shut down; automatic recovery in progress LOG: ReadRecord: record with zero length at 2/1C591C8C LOG: redo is not required LOG: database system is ready PANIC: open of /mnt/diske/skladdb/pg_clog/09FD failed: No such file or directory LOG: statement: COPY public.a_acc (ids, ids_firma, ids_debit, ids_kredit, date_op, ids_vid_doc, ids_otdel, ids_papka, ids_papka1, ids_papka2, parv_doc, doc, nomer, val, voborot, kurs, oborotlv, zab1, zab2, kod1, kod2, kod3, addkod, pp, instime, modtime, ids_slu) TO stdout; LOG: server process (pid 1260) was terminated by signal 6 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing shared memory and semaphores LOG: database system was interrupted at 2003-10-27 14:37:10 EET How can I found the reason for this problem. Also we try to use pg in production and to replace oracle, but it looks very unstable with this corruption. Exist any way to prevent pg from this error (for example real time backup for changes or realtime replication?). We are using pg from 3 y. and we was very happy, but in production it looks not so stable. many thanks, ivan. ---(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] another pg database corruption?
Hi Andrew, Andrew Sullivan wrote: > On Mon, Oct 27, 2003 at 06:21:40PM +0100, pginfo wrote: > > Hi all, > > I am using pg 7.3.4 on linuz red hat 7.3 and reiserFS. > > How sure are you that your patch level for your kernel is good? ISTR > some issues with reiserfs on some versions of the recent kernels, but > there've been so many filesystem problems with Linux over the last > couple years that my recollection is probably not all it might be. > I do not have made any patchin on my kernel.I used the standart reiserfs with my linux distro. If it exists better filesystem I am ready to use it. Also I have bad had bad results with ext3. In general I need journal file system. > Anyway, I would start wondering about hardware. Try badblocks on > your disk. Note that to produce useful results, you may have to do > the destructive tests. You'll be wanting to back up your data first. I can not:). If I try pg_dump the pg crashes.One of my problems is to restore this data if possible. I have a cron script that makes pg_dump every 3 h., but it is not possible to collect the missing data, because we have many users. >From this position I spoke, that I need to be sure that pg is very stable. > There have been occasional reports of clog files being destroyed -- > search the archives for some quick fixes, but note that you are > likely to have some inconsistent data. Any idea for fixing this data is wellcome. I will check the data inconsistent. > So far, all the problems have > been attributable to hardware, but nobody is ruling out a bug, if it > is reproducable. We used the system 30 days without any reboot or stop. The last one was for java upgrade and do not any problems.We do not have any changes in hardware ( I will not say it is not hardware). > I think Tom Lane is especially interested in > looking at cases like this; or he was last time I talked to him about > it. > > A > > -- > > Andrew Sullivan 204-4141 Yonge Street > Afilias CanadaToronto, Ontario Canada > <[EMAIL PROTECTED]> M2P 2A8 > +1 416 646 3304 x110 > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings regards, ivan. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] another pg database corruption?
Andrew Sullivan wrote: > On Tue, Oct 28, 2003 at 03:12:37PM +0100, pginfo wrote: > > > > > > > I do not have made any patchin on my kernel.I used the standart reiserfs > > with my linux distro. > > Yes, but have you kept up to date with new kernel releases from Red > Hat? Realy no.But I have many instalations with this version and the same config. And all this is working well for long periode. The problem is that the corrupted was the biggest one. > They're pretty good about releasing patched kernels if there is > a problem. > > > > your disk. Note that to produce useful results, you may have to do > > > the destructive tests. You'll be wanting to back up your data first. > > > > I can not:). If I try pg_dump the pg crashes.One of my problems is to > > restore this data if possible. > > Shut down the database, I'm afraid, and copy the data directory > before you start doing things. > I did it. > > >From this position I spoke, that I need to be sure that pg is very stable. > > So far as I've heard, the cases of clog file corruption have all been > related to hardware. You'd best think about replacing your disk or > your controller. The system was with 3 hdd. One for my application server one for pg and one for archives.After the first problem I execute initdb -D on my archive disk, got the last possible backup and start to insert data. All was well, but after 10 h of work pg stops ( I sendet this error here). Also later I tryed to recreate the db on my last disk and this time I had problems by restoring the data. So I think I do not have problem with my hdd ( if I do not have problem with all 3 hdd at same time, or with controller). > Have you had any crashes lately? Anything unusual? > Are you using ECC RAM? (If not, are you sure you don't have bad RAM? > If you got a bad bit written at the wrong time, you'd have a real > mess.) My rad is 1G ECC each. I am using 2 G of RAM on this box.. > > > > Any idea for fixing this data is wellcome. I will check the data > > inconsistent. > > As I understand it, you need to zero out the file with dummy data in > order to get going again. You really need to plough the archives for > what to do, though. I've never had to do this. > > A > > -- > > Andrew Sullivan 204-4141 Yonge Street > Afilias CanadaToronto, Ontario Canada > <[EMAIL PROTECTED]> M2P 2A8 > +1 416 646 3304 x110 > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend reagards, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] vacuum full problem
Hi, I am running pg 7.3.4 on linux red hat 9.0. If I try to execute vacuum full analyze verbose, the pg vacuum some tables and hang after this lines: INFO: --Relation pg_toast.pg_toast_16408-- INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Index pg_toast_16408_index: Pages 1; Tuples 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. After rebooting I can vacuum without any problems. I do not find nothing in my log-file that can point me to this problem. Also the problem comes constantly by me. Any idea or info about this problem? Also I am using pg + java (jboss), but I think vacuum is working normal with it and I have many instalations without this problem. regards, ivan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] vacuum full problem
Hi, I try to reindex and reindex run ok, but the vacuum still not running. regards, ivan. Antonis Antoniou wrote: > pginfo wrote: > > >Hi, > >I am running pg 7.3.4 on linux red hat 9.0. > > > >If I try to execute vacuum full analyze verbose, the pg vacuum some > >tables and hang after this lines: > > > >INFO: --Relation pg_toast.pg_toast_16408-- > >INFO: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, > >Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space > >0/0; EndEmpty/Avail. Pages 0/0. > >CPU 0.00s/0.00u sec elapsed 0.00 sec. > >INFO: Index pg_toast_16408_index: Pages 1; Tuples 0. > >CPU 0.00s/0.00u sec elapsed 0.00 sec. > > > >After rebooting I can vacuum without any problems. > >I do not find nothing in my log-file that can point me to this problem. > >Also the problem comes constantly by me. > >Any idea or info about this problem? > >Also I am using pg + java (jboss), but I think vacuum is working normal > >with it and I have many instalations without this problem. > > > Try to "reindex table pg_toast.pg_toast_16408;" Maybe an index is broken. > > Many Thanks > Antonis > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] vacuum full problem
Hi, I can not be sure if it is not the case. But we are usin this system on a number of servers and it happen only by one. Can I with a pg_locks help detect the query that is running? regards, ivan. Stephan Szabo wrote: > On Tue, 11 Nov 2003, Antonis Antoniou wrote: > > > Guys, does anyone has any idea? > > Are you sure that vacuum is not just waiting on a lock that some other > transaction is holding? I'd suggest checking the contents of pg_locks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] vacuum full problem
Hi, Thanks for the help. The result by is: acc01=# select * from pg_locks; relation | database | transaction | pid | mode | granted --+--+-+---+-+- 16757 |16976 | | 23169 | AccessShareLock | t 17062 |16976 | | 1372 | AccessShareLock | t 17060 |16976 | | 1372 | AccessShareLock | t | | 116303 | 1372 | ExclusiveLock | t | | 166846 | 23169 | ExclusiveLock | t 16995 |16976 | | 1372 | AccessShareLock | t 17056 |16976 | | 1372 | AccessShareLock | t 17038 |16976 | | 1372 | AccessShareLock | t (8 rows) And only transaction 116303 is the problem. Also it is production system with many transaction/sec and I am looking dor a way to detect what query is in this transaction ( if possible). The system is relativ big and it is dificult to detect this problem only from transacton number, but we will read the code to find the bug. At this point is is clear that we do not have pg bug and it is good news for me. regards, ivan Stephan Szabo wrote: > On Tue, 11 Nov 2003, pginfo wrote: > > > Hi, > > I can not be sure if it is not the case. > > But we are usin this system on a number of servers and it happen only by > > one. > > Can I with a pg_locks help detect the query that is running? > > It won't directly tell you what statement is running (although my guess is > also that it's probably something like an idle in transaction state > backend), but it can tell you if that's the problem probably. > > You'd see something like: > ... > 21488 |17057 | | 8613 | AccessShareLock | t > ... > 21488 |17057 | | 8626 | AccessExclusiveLock | f > > Which is basically saying that the command in pid 8626 is waiting on the > lock that pid 8613 has. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] vacuum full problem
Hi, Stephan Szabo wrote: > On Tue, 11 Nov 2003, pginfo wrote: > > > The result by is: > > acc01=# select * from pg_locks; > > relation | database | transaction | pid | mode | granted > > --+--+-+---+-+- > > 16757 |16976 | | 23169 | AccessShareLock | t > > 17062 |16976 | | 1372 | AccessShareLock | t > > 17060 |16976 | | 1372 | AccessShareLock | t > > | | 116303 | 1372 | ExclusiveLock | t > > | | 166846 | 23169 | ExclusiveLock | t > > 16995 |16976 | | 1372 | AccessShareLock | t > > 17056 |16976 | | 1372 | AccessShareLock | t > > 17038 |16976 | | 1372 | AccessShareLock | t > > (8 rows) > > Is that while the vacuum is running or just a general state that you might > run the vacuum in? > It looks as general state. > > And only transaction 116303 is the problem. > > Also it is production system with many transaction/sec and I am looking dor a > > way to detect what query is in this transaction ( if possible). > > The system is relativ big and it is dificult to detect this problem only from > > transacton number, but we will read the code to find the bug. > > Well, you can turn on the statement statistics stuff and/or the > statement logging stuff to help you find what the various transactions > are doing. I'd wonder if you're maybe not closing a transaction after > it's completed its work though and so the locks are sitting around. I will do it. It is possible to be one not closed transaction, but in this case nobody will be able to modify this table (tables) and the system will stop to respond. The paradox is that the system works well without any problems and on this basis I wrote that (at the beginning) that the problem is by vacuum. How big is the penalty for statistic on? Generaly I think we will be able to found the problem quick. regards, ivan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] vacuum full problem
Stephan Szabo wrote: > On Tue, 11 Nov 2003, pginfo wrote: > > > Stephan Szabo wrote: > > > > > On Tue, 11 Nov 2003, pginfo wrote: > > > > > > > The result by is: > > > > acc01=# select * from pg_locks; > > > > relation | database | transaction | pid | mode | granted > > > > --+--+-+---+-+- > > > > 16757 |16976 | | 23169 | AccessShareLock | t > > > > 17062 |16976 | | 1372 | AccessShareLock | t > > > > 17060 |16976 | | 1372 | AccessShareLock | t > > > > | | 116303 | 1372 | ExclusiveLock | t > > > > | | 166846 | 23169 | ExclusiveLock | t > > > > 16995 |16976 | | 1372 | AccessShareLock | t > > > > 17056 |16976 | | 1372 | AccessShareLock | t > > > > 17038 |16976 | | 1372 | AccessShareLock | t > > > > (8 rows) > > > > > > Is that while the vacuum is running or just a general state that you might > > > run the vacuum in? > > > > > > > It looks as general state. > > > > > > And only transaction 116303 is the problem. > > > > Also it is production system with many transaction/sec and I am looking dor a > > > > way to detect what query is in this transaction ( if possible). > > > > The system is relativ big and it is dificult to detect this problem only from > > > > transacton number, but we will read the code to find the bug. > > > > > > Well, you can turn on the statement statistics stuff and/or the > > > statement logging stuff to help you find what the various transactions > > > are doing. I'd wonder if you're maybe not closing a transaction after > > > it's completed its work though and so the locks are sitting around. > > > > I will do it. > > It is possible to be one not closed transaction, but in this case nobody will be > > able to modify this table (tables) and > > the system will stop to respond. The paradox is that the system works well without > > Not necessarily. People are going to be able to insert/update/delete from > the tables (the locks are AccessShareLock) because those don't get a > conflicting table lock. They're not going to be able to do things like > vacuum full or alter table however because those do. > Can you point me to any place in docs to read more detailed about locks and statistic ( I have idea, butt also I will to know more if possible). > > How big is the penalty for statistic on? > > I'm not sure, but you can turn it on until you find it and then turn it > off again. regards, ivan. ---(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] vacuum full problem
Stephan Szabo wrote: > On Tue, 11 Nov 2003, pginfo wrote: > > > > > It is possible to be one not closed transaction, but in this case nobody will > > > > be > > > > able to modify this table (tables) and > > > > the system will stop to respond. The paradox is that the system works well > > > > without > > > > > > Not necessarily. People are going to be able to insert/update/delete from > > > the tables (the locks are AccessShareLock) because those don't get a > > > conflicting table lock. They're not going to be able to do things like > > > vacuum full or alter table however because those do. > > > > > > > Can you point me to any place in docs to read more detailed about locks and > > statistic ( > > I have idea, butt also I will to know more if possible). > > Well, for a list of the lock levels and some examples of where they're > used you might want to see: > http://www.postgresql.org/docs/7.3/static/explicit-locking.html > Thanks, it is good.regards, ivan. > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] pg 7.4 on debian
Hi all, I just tryed to compule 7.4 on debian 3.0. It is my first try to compile pg on debian ( I was using redhat to this moment), sorry for my question. Aftre ./configure --bindir=/usr/bin --libdir=/usr/lib/pgsql I recive error: configure: error: zlib library not found If you have zlib already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-zlib to disable zlib support. And also one error message for readline library. So I am not familiar with debian, but by redhat I have all this libraries after OS installation. Exist some one that have installed 7.4 on debian and that can help me in this case? regards, ivan. ---(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 7.4 on debian
Hi, Sam Barnett-Cormack wrote: > Is pg7.4 not available through the apt system at all? > I do not know.I prefer to compile pg self. And also I found the problem. I needet libreadline4-dev and zlib1g-dev. It is working now. regards, ivan. > On Sun, 23 Nov 2003, pginfo wrote: > > > Hi all, > > > > I just tryed to compule 7.4 on debian 3.0. > > It is my first try to compile pg on debian ( I was using redhat to this > > moment), sorry for my question. > > > > Aftre ./configure --bindir=/usr/bin --libdir=/usr/lib/pgsql > > > > I recive error: > > configure: error: zlib library not found > > If you have zlib already installed, see config.log for details on the > > failure. It is possible the compiler isn't looking in the proper > > directory. > > Use --without-zlib to disable zlib support. > > > > And also one error message for readline library. > > > > So I am not familiar with debian, but by redhat I have all this > > libraries after OS installation. > > > > Exist some one that have installed 7.4 on debian and that can help me in > > this case? > > > > regards, > > ivan. > > > > > > ---(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 > > > > -- > > Sam Barnett-Cormack > Software Developer | Student of Physics & Maths > UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] filesystem suggestion for pg
Hi , We are using pg as oracle alternative on ~30 production db servers. Also we are planing to migrate all oracle servers to pg if we can get stable config. After 6 m. of intensive use of systems by diferent customers we do not know what for filesystem to install. In this time we are using linux red hat (7.3 or 9.0) with ext3 or reiserfs. For 6 m. we have 6 pg crashes and total data loses (it is very bad result for us). The last one was today on linux red hat 9.0 and reiserfs after power down (in most cases the problems are detected after power down). Only for info I send the log: LOG: checkpoint record is at 0/5EF69F40 LOG: redo record is at 0/5EF69F40; undo record is at 0/0; shutdown FALSE LOG: next transaction id: 165597; next oid: 1931805 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/5EF69F80 PANIC: Invalid page header in block 93 of 1919556 LOG: startup process (pid 1253) was terminated by signal 6 LOG: aborting startup due to startup process failure Also for this time we do not have any problems with oracle servers (and we have had many power downs). I belive it is not pg bug, but in all cases the only afected data are this by pg. So, my question: We will to continue to use pg on linux, but I will be hapy to find info about what for filesystem to use (also we can change the linux distro to debian or suse if needet, but not to freebsd) and what mounting options to use. We found that ext3 is less stable comparing to reiserfs and we need to use fsck. We are using ~300 tables with ~3M rows (max) . Also we are using mostly dual pentium + IDE disks. regards, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] compiling 7.4 --with-java
Hi, I try to rebuild my pg 7.4 to get generated jdbc driver. If I run gmake it stops with error (without --with-java option I do not have any problems to complite it). I send the error message (one part from message): gmake[5]: Nothing to be done for `all'. gmake[5]: Leaving directory `/pginst/postgresql-7.4/src/port' gmake[4]: Leaving directory `/pginst/postgresql-7.4/src/interfaces/ecpg/preproc' gmake[3]: Leaving directory `/pginst/postgresql-7.4/src/interfaces/ecpg' gmake[3]: Entering directory `/pginst/postgresql-7.4/src/interfaces/jdbc' /usr/bin/ant -buildfile ./build.xml all Buildfile: ./build.xml all: prepare: check_versions: check_driver: driver: [copy] Copying 1 file to /pginst/postgresql-7.4/src/interfaces/jdbc/org/postgresql [echo] Configured build for the JDBC3 edition driver with NO SSL compile: [javac] Compiling 77 source files to /pginst/postgresql-7.4/src/interfaces/jdbc/build [javac] /pginst/postgresql-7.4/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Statement.java:28: error: Invalid checked exception class `java.sql.SQLException' in `throws' clause. The exception must be a subclass of an exception thrown by `org.postgresql.jdbc1.AbstractJdbc1Statement.createRefCursorResultSet(java.lang.String)' from class `org.postgresql.jdbc1.AbstractJdbc1Statement'. [javac] public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException [javac] ^ [javac] /pginst/postgresql-7.4/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3PreparedStatement.java:23: error: Invalid checked exception class `java.sql.SQLException' in `throws' clause. The exception must be a subclass of an exception thrown by `org.postgresql.jdbc1.AbstractJdbc1Statement.createRefCursorResultSet(java.lang.String)' from class `org.postgresql.jdbc1.AbstractJdbc1Statement'. [javac] public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException [javac] ^ [javac] /pginst/postgresql-7.4/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3PoolingDataSource.java:29: error: Method `org.postgresql.jdbc2.optional.PoolingDataSource.getDataSource(java.lang.String)' was defined with return type `org.postgresql.jdbc2.optional.PoolingDataSource' in class `org.postgresql.jdbc2.optional.PoolingDataSource'. [javac]static Jdbc3PoolingDataSource getDataSource(String name) [javac] ^ [javac] /pginst/postgresql-7.4/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3CallableStatement.java:23: error: Invalid checked exception class `java.sql.SQLException' in `throws' clause. The exception must be a subclass of an exception thrown by `org.postgresql.jdbc1.AbstractJdbc1Statement.createRefCursorResultSet(java.lang.String)' from class `org.postgresql.jdbc1.AbstractJdbc1Statement'. [javac] public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException [javac] ^ [javac] 4 errors BUILD FAILED file:/pginst/postgresql-7.4/src/interfaces/jdbc/build.xml:116: Compile failed; see the compiler error output for details. Total time: 2 seconds gmake[3]: *** [all] Error 1 gmake[3]: Leaving directory `/pginst/postgresql-7.4/src/interfaces/jdbc' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/pginst/postgresql-7.4/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/pginst/postgresql-7.4/src' gmake: *** [all] Error 2 I have ant installed. My platform is linux redhat AS 3.0. with standart installation and sun java 1.4.2. Any idea for this problem? regards, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] remote access to linux
Hi, use ssh if you need only terminal or vnc if you will need X. regards, ivan. vidhya wrote: Hi, I want to access the Linux server in US from win2k in India. Do you have any service like terminal services client to access the Linux server. Thank u, Regards, Vidhya.
[ADMIN] Trigger example in "C"
Hi, where can I find trigger example writen in "C" ? I have many made in pgSQL, but I have performance problems and I will try to rewrit in "C". regards, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Sorry
Sorry for my question about trigger in "C". A found the good example. regards, ivan. ---(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
[ADMIN] freebsd performance
Hi all, I try to find OS with better performance. At the mement we are using linux RH AS 3.0 and I think it is not the best possible. So my test server: Dual Pentium III 1 GHz 1.5 GB ram 30 GB ATA 7200 HDD. pg 7.4.2 I installed freebsd 5.2.1 (It is my first try on it). and ran the same db and pg setup. So I see 30% slower freebsd box compared to linux RH. My question: exists any special setups into freebsd to get it running faster? Also any good docs about configuring freebsd for postgresql will helpfull . regards, ivan. ---(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] problem with pg_dump
Hi all, We are using pg as db for many erp installations. In general it is working great. But at the monet I have problem (I think it is database corrupted, but I am not sure) by running pg_dump. I receive this message: pg_dump acc01 > dmpacc01 pg_dump: query to obtain list of data types failed: ERROR: More than one tuple returned by a subselect used as an expression. I can not find nothink wong in my log-file. The lastest records are: LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2004-06-13 09:39:03 EEST LOG: checkpoint record is at 3/313382E4 LOG: redo record is at 3/313382E4; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 4046855; next oid: 958527775 LOG: database system is ready WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress WARNING: ROLLBACK: no transaction in progress ERROR: More than one tuple returned by a subselect used as an expression. We are using pg 7.3.4 for this install (we will upgrade to 7.4.x soon). Pls. help me to sort out this problem. It is production db and is important to resolve the problem. regards, ivan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] postgresql DB backing up within specific interval
Hi, I am using linux cron and pg_dump. It is working very well. After you have dump-file you can move it to remote pc. regards, ivan Edgars wrote: Hi! is here anyone familar with subj.? How that could be implemented? I'm using postgresql on Debian system. Is it possible only on the same server where the main DB is placed or also on some other server too? Thank you anyway! Edgars ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Selecting HW RAID controller
Hi all, I am searching for good working HW RAID controller. I will use linux as OS. Also I will use SCSI. I readet the docs about adaptec AHA2120S, but do not find any comments about the driver supprt and how stable is for linux. Any ideas? Of course I will use it for pg. many thanks, ivan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] out of memory error
Hi , I try to make insert into myTable select on pg 8.0.1 and freeBSD 5.3. After ~10 sec. pg returns "out of memory". In log file I found: Portal hash: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used Relcache by OID: 8192 total in 1 blocks; 4040 free (0 chunks); 4152 used Relcache by name: 24576 total in 2 blocks; 13240 free (5 chunks); 11336 used LockTable (locallock hash): 8192 total in 1 blocks; 4056 free (0 chunks); 4136 u sed ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ERROR: out of memory DETAIL: Failed on request of size 340. I have 2Gb RAM on this box. regards, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] out of memory error
Hi, I send some additional info : pginfo wrote:sklad05=# explain analyze select S.IDS_NUM,S.IDS_SKLAD,SUM(S.KOL),S.sernum FROM A_GAR_PROD_R S where ids < 9742465 GROUP BY S.IDS_NUM,S.IDS_SKLAD ,s.sernum limit 10; QUERY PLAN Limit (cost=82281.59..82281.61 rows=10 width=148) (actual time=9373.301..9373.368 rows=10 loops=1) -> HashAggregate (cost=82281.59..82594.48 rows=125156 width=148) (actual time=9373.294..9373.328 rows=10 loops=1) -> Index Scan using a_gar_prod_r_pkey on a_gar_prod_r s (cost=0.00..69766.02 rows=1251557 width=148) (actual time=16.412..4175.948 rows=1237554 loops=1) Index Cond: (ids < 9742465) Total runtime: 9386.082 ms (5 rows) sklad05=# explain analyze select S.IDS_NUM,S.IDS_SKLAD,SUM(S.KOL),S.sernum FROM A_GAR_PROD_R S where ids < 9742466 GROUP BY S.IDS_NUM,S.IDS_SKLAD ,s.sernum limit 10; ERROR: out of memory DETAIL: Failed on request of size 344. sklad05=# explain analyze select S.IDS_NUM,S.IDS_SKLAD,SUM(S.KOL),S.sernum FROM A_GAR_PROD_R S where ids >= 9742466 GROUP BY S.IDS_NUM,S.IDS_SKLAD ,s.sernum limit 10; QUERY PLAN --- Limit (cost=7747.82..7747.84 rows=10 width=148) (actual time=960.984..961.050 rows=10 loops=1) -> HashAggregate (cost=7747.82...27 rows=11783 width=148) (actual time=960.979..961.011 rows=10 loops=1) -> Index Scan using a_gar_prod_r_pkey on a_gar_prod_r s (cost=0.00..6569.60 rows=117822 width=148) (actual time=0.874..431.357 rows=131824 loops=1) Index Cond: (ids >= 9742466) Total runtime: 961.283 ms (5 rows) regards, ivan. Hi , I try to make insert into myTable select on pg 8.0.1 and freeBSD 5.3. After ~10 sec. pg returns "out of memory". In log file I found: Portal hash: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used Relcache by OID: 8192 total in 1 blocks; 4040 free (0 chunks); 4152 used Relcache by name: 24576 total in 2 blocks; 13240 free (5 chunks); 11336 used LockTable (locallock hash): 8192 total in 1 blocks; 4056 free (0 chunks); 4136 u sed ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used ERROR: out of memory DETAIL: Failed on request of size 340. I have 2Gb RAM on this box. regards, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] how to restore corrupted pg?
Hi all, after many months of well working I was very supporaised that my pg do not work. It is produktion server. I checked the pd directory and found that the global directory was destroyed. In my system it is file owned by root( I do not know how is it happen). All other directories are there ( I am usinf about 5 db). Exist any way to recover my global dir and data in my 5 db? Many thanks in avdance, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] URGENT: where to find info about the pg structure
Hi all, I posted today message about pg globals directory corruption. I am using pg 7.3 on linux with reiserfs. My problem is where to find more info about the pg structure. To this moment I created one new db with the same structure as my original. I was able to finf the filename of one from my tables into the new db. Also I have this file from the corrupted system. I stoped the db and make copy over the new file. After bd start I am not able to any data in pg. Pg report: LOG: database system is shut down LOG: database system was shut down at 2003-05-29 17:46:10 EEST LOG: checkpoint record is at 25/2B043768 LOG: redo record is at 25/2B043768; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 4607583; next oid: 52623925 LOG: database system is ready PANIC: read of clog file 4, offset 196608 failed: Success LOG: statement: vacuum full analyze verbose a_nomen; LOG: server process (pid 1455) was terminated by signal 6 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing shared memory and semaphores LOG: database system was interrupted at 2003-05-29 17:49:18 EEST LOG: checkpoint record is at 25/2B043768 LOG: redo record is at 25/2B043768; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 4607583; next oid: 52623925 LOG: database system was not properly shut down; automatic recovery in progress FATAL: The database system is starting up LOG: ReadRecord: record with zero length at 25/2B0437A8 LOG: redo is not required LOG: database system is ready LOG: smart shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2003-05-29 17:49:58 EEST LOG: checkpoint record is at 25/2B0437E8 LOG: redo record is at 25/2B0437E8; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 4607583; next oid: 52623925 LOG: database system is ready Is it possible to replace the files with my "old" files and if yes how? Any idea will help. regards, ivan. ---(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
[ADMIN] pg 7.3.4 and linux box crash
Hi, I am runing linux red hat 7.3 (standart install) on dual athlon box , 1 GB ram and pg 7.3.4. If I try to access with pgAdmin one from my tables (i contains ~ 1 M records) the linux box crashes. In my pg log I can find: ERROR: Invalid page header in block 5604 of a_acc before this error I see also : FATAL: Database "template0" is not currently accepting connections ERROR: Relation "pg_relcheck" does not exist and ERROR: 'ksqo' is not a valid option name I am using reiserFS on this box and do not find any other problems with it. Can I solve this problem or I need to recreate the datebase ( The table and the database are created befor 2 days)? regards, ivan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] pg 7.3.4 and linux box crash
Hi, Tom Lane wrote: > pginfo <[EMAIL PROTECTED]> writes: > > In my pg log I can find: > > ERROR: Invalid page header in block 5604 of a_acc > > You have a corrupt table. Can you drop and recreate a_acc? > Yes I can.The problem is that it is happen for 4 times in this week. It is happen on 2 diferent servers ( the second is production, and it is bad). I have had similar problem with pg 7.3.3. and some one from the list wrot that it is bug and I need to upgrade to 7.3.4. I have made the upgrade, but I am not sure if this bug exist in pg 7.3.4. If yes, what for version will be best to use? > > before this error I see also : > > FATAL: Database "template0" is not currently accepting connections > > ERROR: Relation "pg_relcheck" does not exist > > and > > ERROR: 'ksqo' is not a valid option name > > Seems you are using a rather out-of-date version of pgAdmin. > Yes, it is true.I am waiting for version 3. > regards, tom lane regards, ivan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] pg 7.3.4 and linux box crash
Tom Lane wrote: > pginfo <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> You have a corrupt table. Can you drop and recreate a_acc? > > > Yes I can.The problem is that it is happen for 4 times in this week. > > Oh? Perhaps you should be looking at the contents of the complained-of > pages to try to see what's going on. Is there any pattern to the failures? I do not see!I will search the problem and any additional info is wellcome. > > > > It is happen on 2 diferent servers > > Even so, I wouldn't rule out a hardware problem, especially if the > servers are identical hardware setups. Have you run memtest86 and > badblocks? > No, but on this servers we are running jboss and so on and do not find any problems.The servers are not identical, but linux version and file system are the same. Also the pg config is the same. > regards, tom lane thanks, ivan ---(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 7.3.4 and linux box crash
scott.marlowe wrote: > On Fri, 12 Sep 2003, pginfo wrote: > > > Hi, > > I am runing linux red hat 7.3 (standart install) > > on dual athlon box , 1 GB ram and pg 7.3.4. > > > > If I try to access with pgAdmin one from my tables (i contains ~ 1 M > > records) > > the linux box crashes. > > WAIT. Do you mean you get a kernel panic? Or the box locks up tight? Or > the box reboots? > > Or does Postgresql crash? > > Or does the linux box complain about a bad hard drive and stop running > postgresql? > > If linux is crashing, that is NOT postgresql's fault. An app can't crash > an OS without root level access, and Postgresql ain't got that. > It is crashing the linux box. Not rebooting, not kernel panic, but only stop to respond.On the console if I type reboot it will not to reboot and so on. But it crash only if I start intensive operations on pg. And also my problem are this messages into the log. At hte weekend I will make some detailed tests on OS and hardware to see if it is the problem. It will be the best case for me if the problem is not in pg. > > In my pg log I can find: > > ERROR: Invalid page header in block 5604 of a_acc > > > > before this error I see also : > > FATAL: Database "template0" is not currently accepting connections > > ERROR: Relation "pg_relcheck" does not exist > > > > and > > > > ERROR: 'ksqo' is not a valid option name > > This sounds like you are using an older pgaccess on a newer postgresql > database. Once upon a time, apps could connect to template0. That is no > longer the case. > > ---(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 regards, ivan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] problem installing pgAdmin 3 on red hat 7.3
Hi, I try to install the latest pgAdmin3 on my workstation. It is running red hat 7.3. I make the installation from source. By running ./configure I got this message: you must specify a valid pgsql instalation wit --with-pgsql=DIR. I installed also the latest pg 7.3.4. ( I do not deen it on my workstation), but I do not know where is this pgsql instalation dir. regards, ivan. ---(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
[ADMIN] sorry!
Sorry the last message was for pgAdmin mailing list. Pls. ignore it. It was my type mistake and I am very sorry. regards, ivan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] pg 7.3.4 and linux box crash
Hi, yes I can type reboot, but the system can not reboot. I tested the memory (it take ~20 h - the maximum test) without any problems. I do not see any reason to think that the problem is into the HW/OS. At the moments I install red hat 9.0 to be sure that the problem is not by kernel. regards, ivan. Matt Clark wrote: > > > It is crashing the linux box. Not rebooting, not kernel panic, > > but only stop > > > to respond.On the console if I type reboot it will not to > > reboot and so on. > > > But it crash only if I start intensive operations on pg. > > If you can type 'reboot' then surely it hasn't stopped responding? > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
