Re: [GENERAL] backup-strategies for large databases

2011-08-14 Thread Mikko Partio
On Sun, Aug 14, 2011 at 12:44 AM, MirrorX mirr...@gmail.com wrote: the issue here is that the server is heavily loaded. the daily traffic is heavy, which means the db size is increasing every day (by 30 gb on average) and the size is already pretty large (~2TB). at the moment, the copy of

Re: [GENERAL] Incrementally Updated Backups

2010-09-13 Thread Mikko Partio
I'm interested in the incrementally updated backups scenario described in section 25.6 of the Postgres 9 documentation. I've configured streaming replication for my warm standby server. Step 2 in this procedure is to note?pg_last_xlog_replay_location at the end of the backup. However

Re: [GENERAL] Incrementally Updated Backups

2010-09-13 Thread Mikko Partio
That section has been removed from the current 9.0 docs because we are unsure it works. Is the feature (or the documentation) still being worked on, or is pg_dump the only way to take a backup of a warm standby while the database is running? I don't think you can take a pg_dump of

Re: [GENERAL] Postgres Clustering Options

2009-11-12 Thread Mikko Partio
On Fri, Nov 13, 2009 at 1:47 AM, David Kerr d...@mr-paradox.net wrote: In your enviornment, are the applications able to recover automatically after a DB failover? For exmaple, we're using Java/JDBC connections +Geronimo we're researching whether or not JDBC/Geronimo would be able to retry

Re: [GENERAL] Postgres Clustering Options

2009-11-11 Thread Mikko Partio
On Wed, Nov 11, 2009 at 7:28 PM, David Kerr d...@mr-paradox.net wrote: What I plan on doing is: Postgres installed on a Cluster configured in active/passive (both pointing to the same SAN (If PG or the OS fails we trigger a failover to the passive node) Is this a common/reccomended method

Re: [GENERAL] connecting using libpq breaks printf

2009-02-18 Thread Mikko Partio
On Wed, Feb 18, 2009 at 4:47 PM, Joey Morris rjmorri...@gmail.com wrote: This is my first attempt at using libpq, and I'm running across a strange problem. Here is my bare-bones program: #include stdio.h #include libpq-fe.h int main(int argc, char **argv) { PGconn *conn;

Re: [GENERAL] two or more pg installations running as the same user

2008-11-25 Thread Mikko Partio
On Tue, Nov 25, 2008 at 12:37 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED]wrote: hey folks, I am thinking about testing enviroment here, and gotta setup temporary instalation of postgres for that. More than one, because software operates on few different connections, to different databases. I

[GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
Hello all Postgres version 8.3.1 I just created a bunch of tables (~10) with identical structure; all tables have 6 foreign key references to other tables and a primary key. To my surprise, some of the tables were created ok, some missed primary key and some didn't get created at all. Postgres

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 12:20 PM, Mikko Partio [EMAIL PROTECTED] wrote: On Tue, Apr 8, 2008 at 11:27 AM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): Hello all Postgres version 8.3.1 I just created a bunch of tables (~10) with identical structure; all

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 1:19 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): I little investigation showed that there is duplicate row in pg_class: # select oid from pg_class group by oid having count(*) 1 ; oid 294397 (1 row) Could you check

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 1:45 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): Should I try remove one of the duplicate rows from pg_class? Try it with caution. You should use ctid column to refer to exact row. Try before: select oid, ctid, xmin, xmax, cmin, cmax

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 11:27 AM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): Hello all Postgres version 8.3.1 I just created a bunch of tables (~10) with identical structure; all tables have 6 foreign key references to other tables and a primary key. To my

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 2:49 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): On Tue, Apr 8, 2008 at 1:45 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): Should I try remove one of the duplicate rows from pg_class? Try it with caution. You

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 1:45 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): Should I try remove one of the duplicate rows from pg_class? Try it with caution. You should use ctid column to refer to exact row. Ok I removed the faulty tuple and nothing catastrophical

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 5:26 PM, Mikko Partio [EMAIL PROTECTED] wrote: Ok I removed the faulty tuple and nothing catastrophical happened. I can do a pg_dump now, but I still can't remove the one remaining table: # drop table xyz ; ERROR: too many trigger records found for relation xyz Any

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 6:38 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: Mikko Partio napsal(a): On Tue, Apr 8, 2008 at 5:26 PM, Mikko Partio [EMAIL PROTECTED] wrote: Ok I removed the faulty tuple and nothing catastrophical happened. I can do a pg_dump now, but I still can't remove

Re: [GENERAL] too many trigger records found for relation xyz

2008-04-08 Thread Mikko Partio
On Tue, Apr 8, 2008 at 9:30 PM, Tom Lane [EMAIL PROTECTED] wrote: Mikko Partio [EMAIL PROTECTED] writes: On Tue, Apr 8, 2008 at 6:38 PM, Zdenek Kotala [EMAIL PROTECTED] wrote: It is really strange. It would be nice to have reproduce scenario. Can you run same DDL command which invoke

Re: [GENERAL] SPI-functions and transaction control

2008-02-20 Thread Mikko Partio
On Feb 19, 2008 12:12 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Mikko Partio escribió: Now, I was wondering if a c function would be faster, and with the help of the manual I have written a function that can insert tuples from one table to another. As the manual states ( http

[GENERAL] SPI-functions and transaction control

2008-02-17 Thread Mikko Partio
Hello list I am trying to write a function in c that would 'merge' two tables together. The idea is that we insert rows from one table to another, and if there is a constraint violation, update the old row with the new row. I have done this succesfully with plpgsql, but alas, the tables are so

[GENERAL] rpm's for 8.3 beta 4

2007-12-03 Thread Mikko Partio
Hello is there going to be an rpm release of beta 4 (for RHEL 5)? It seems that beta 2 did have red hat rpms but beta 3 did not. Regards Mikko

Re: [GENERAL] Populating large DB from Perl script

2007-11-03 Thread Mikko Partio
On Nov 2, 2007 8:45 PM, Kynn Jones [EMAIL PROTECTED] wrote: PS: As an aside to the list, as a programmer, when I'm starting out in language, I learn more than I can say from reading source code written by the experts, but for some reason I have had a hard time coming across expertly written

Re: [GENERAL] Automating Backup Restor

2007-10-24 Thread Mikko Partio
On 10/24/07, smiley2211 [EMAIL PROTECTED] wrote: Hello all, Does someone have a script that backups a database from one server and restores it to another server??? I am NEW to Postgresql so I am starting from scratch... so, in essence - what I want to do is (I CURRENTLY DO THIS

Re: [GENERAL] multiple row insertion

2007-10-04 Thread Mikko Partio
On 10/4/07, test tester [EMAIL PROTECTED] wrote: In MySQL, I can insert multiple rows like this: insert into cars values(5, toyota),(5,ford), etc. How can I do something similiar in PostgreSQL? Exactly the same way. Make sure though that your pgsql is new enough version (8.2 ?). Regards

Re: [GENERAL] Problem dropping table

2007-09-18 Thread Mikko Partio
On 9/18/07, Ken Logan [EMAIL PROTECTED] wrote: On Tue, 2007-09-18 at 11:10 -0700, Alan Hodgson wrote: On Tuesday 18 September 2007 10:30, Ken Logan [EMAIL PROTECTED] wrote: When we try to drop the table we get the error: ERROR: member_pkey is an index You have to remove the table from

Re: [GENERAL] Symlinking (specific) tables to different Drives

2007-09-03 Thread Mikko Partio
On 9/4/07, Ow Mun Heng [EMAIL PROTECTED] wrote: Hi, I'm running out of space on one of my partitions and I still have not gotten all the data loaded yet. I've read that one could symlink the pg_pg_xlog directory to another drive. I'm wondering if I can do the same for specific tables as

Re: [GENERAL] Symlinking (specific) tables to different Drives

2007-09-03 Thread Mikko Partio
On 9/4/07, Ow Mun Heng [EMAIL PROTECTED] wrote: On Tue, 2007-09-04 at 07:09 +0300, Mikko Partio wrote: On 9/4/07, Ow Mun Heng [EMAIL PROTECTED] wrote: Hi, I'm running out of space on one of my partitions and I still have not gotten all the data

Re: [GENERAL] out of memory error

2007-08-24 Thread Mikko Partio
On 8/23/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: You've got it completely wrong. By setting shared_buffers to 2GB it means no-one can use it. It's not postgres that's running out of memory, it's the rest of your system. Set it to something sane like 128MB or maybe smaller.

Re: [GENERAL] PostgreSQL clustering (shared disk)

2007-08-17 Thread Mikko Partio
On 8/16/07, Douglas McNaught [EMAIL PROTECTED] wrote: Devrim GÜNDÜZ [EMAIL PROTECTED] writes: What I'm pondering here is that is the cluster able to keep the postmasters synchronized at all times so that the database won't get corrupted. Keep all the $PGDATA in the shared disk. That

Re: [GENERAL] PostgreSQL clustering (shared disk)

2007-08-17 Thread Mikko Partio
On 8/17/07, Hannes Dorbath [EMAIL PROTECTED] wrote: On 17.08.2007 11:12, Mikko Partio wrote: Maybe I'm just better off using the more simple (crude?) method of drbd + heartbeat? Crude? Use what you like to use, but you should keep one thing in mind: If you don't know the software you

[GENERAL] PostgreSQL clustering (shared disk)

2007-08-16 Thread Mikko Partio
Hello list, I have a mission to implement a two-node active-passive PostgreSQL cluster. The databases at the cluster are rather large (hundreds of GB's) which opts me to consider a shared disk environment. I know this is not natively supported with PostgreSQL, but I have been investigating the

Re: [GENERAL] Moving to postgresql and some ignorant questions

2007-08-14 Thread Mikko Partio
On 8/14/07, Bill Moran [EMAIL PROTECTED] wrote: But the rule is, if any query within the transaction errors, then all queries within the transaction are rolled back. This is the default behaviour, but with psql and ON_ERROR_ROLLBACK parameter the behaviour can be changed. See

Re: [GENERAL] Unexpected crash

2007-07-19 Thread Mikko Partio
On 7/19/07, Tom Lane [EMAIL PROTECTED] wrote: Mikko Partio [EMAIL PROTECTED] writes: I installed the latest version from rpms and everythings ok, except when I connect to a db with psql and press shift+return the backend crashes with Segmentation fault! This is not a backend crash, you

Re: [GENERAL] Unexpected crash

2007-07-19 Thread Mikko Partio
On 7/19/07, Richard Huxton [EMAIL PROTECTED] wrote: Mikko Partio wrote: OK. This is RH Enterprise Server, then? Yes. cat /etc/issue Red Hat Enterprise Linux ES release 3 (Taroon Update 8) Kernel \r on an \m Is there a server log-line saying you have a sig-11 crash? No that is the only

[GENERAL] Unexpected crash

2007-07-18 Thread Mikko Partio
Hello all, I installed the latest version from rpms and everythings ok, except when I connect to a db with psql and press shift+return the backend crashes with Segmentation fault! I guess the problem is with my installation but I don't know how to debug. It's not a very disconcerning thing per

Re: [GENERAL] tables are not listable by \dt

2007-07-02 Thread Mikko Partio
On 7/2/07, Rajarshi Guha [EMAIL PROTECTED] wrote: Hi, we recently upgraded our Postgres instllation from 7.4 to 8.2 by doing a dump and restore. Howveer after logging into the database (as a user that is not the superuser) and doing \dt I get the error: No relations found Are you using the

Re: [GENERAL] data partitions across different nodes

2007-06-25 Thread Mikko Partio
On 6/26/07, 金星星 [EMAIL PROTECTED] wrote: Are there any solutions based on PostgreSQL that can support distributing partitions (horizontal fragmentations) across different nodes. It doesn't need to support distributed transaction, since data inconsistent is not a critical problem in my

Re: [GENERAL] Permission denied to create database

2007-05-04 Thread Mikko Partio
$ sudo -u postgres psql -c grant all on tablespace pg_default to joe Password: GRANT $ createdb -U joe joejunkdb createdb: database creation failed: ERROR: permission denied to create database How about ALTER ROLE joe CREATEDB Regards MP ---(end of

[GENERAL]

2007-04-16 Thread Mikko Partio
Nico Sabbi wrote: but cp /tmp/pg//00010021 pg_xlog/RECOVERYXLOG looks suspicious to me. Works for me [clip from process listing]: postgres 12864 12863 0 18:36 ?00:00:00 cp /wal/000100E10035 pg_xlog/RECOVERYXLOG I think RECOVERYXLOG should be

Re: [GENERAL] Anyone know where I can get an 8.2.3 binary for ubuntu?

2007-03-31 Thread Mikko Partio
Subject says it all. Doing a source compile under Debian or Debian-like condition is not an option for the end user. They need an apt-get (the ubuntu equivalent to rpm AFAICT) version. Unfortunately, the latest I can find is 8.1.8 Where's 8.2.3? What ubuntu version do you have? I

Re: [GENERAL] PG Dump 8.2

2007-03-03 Thread Mikko Partio
Joshua D. Drake wrote: Mikko Partio wrote: Laurent ROCHE wrote: Hi, Does anyone know if Ubuntu 6.10 (LTS) has packages for PostgreSQL 8.2, from a recommandable place ? I can not recommend my clients to use 8.2 if there's nobody supporting the packages (so just compiling from

Re: [GENERAL] PG Dump 8.2

2007-03-02 Thread Mikko Partio
Laurent ROCHE wrote: Hi, Does anyone know if Ubuntu 6.10 (LTS) has packages for PostgreSQL 8.2, from a recommandable place ? I can not recommend my clients to use 8.2 if there's nobody supporting the packages (so just compiling from the source code is not an option). They are right at

Re: [GENERAL] Cast record as text

2007-02-17 Thread Mikko Partio
A. Kretschmer wrote: My original idea was to log changes from different tables to one audit table, and I think tablelog uses separate audit tables for each monitored table? Yes, but with tablelog it is possible to restore any changes, you can restore a table. A blog-entry from Andreas

Re: [GENERAL] Cast record as text SOLVED

2007-02-17 Thread Mikko Partio
Mikko Partio wrote: I agree that the ability to restore changes is quite nice, but my primary goal is to record changes from many tables into one table, and I think tablelog does not offer that. Do you know any way of casting a record to text, or perhaps a different way altogether to audit

Re: [GENERAL] Cast record as text

2007-02-14 Thread Mikko Partio
Why do you want to reinvent the wheel? http://pgfoundry.org/projects/tablelog/ But it use a separate log-table per table. Andreas My original idea was to log changes from different tables to one audit table, and I think tablelog uses separate audit tables for each monitored table?

Re: [GENERAL] Client connection: Port 5432 / Postgres

2007-02-08 Thread Mikko Partio
Nicolas Gignac wrote: I have installed Postgres 8.2 on a internal server having Windows Server 2003 (IIS 6) up and running. - I have configure the hp_config file to: host all 0.0.0.0./0md5 ^

Re: [GENERAL] CONNECT privilege

2006-11-26 Thread Mikko Partio
Joris Dobbelsteen wrote: When using pgAdmin-III it does not display the granted CONNECT priviledge. Also when doing GRANT CONNECT FOR DATABASE db TO user/group it succeeds, but I fail to observe its effects: i.e. users are not allowed to a database. Have you modified pg_hba.conf to

Re: [GENERAL] Simple stored procedure examples?

2006-11-04 Thread Mikko Partio
novnov wrote: Thanks to both of you for responding. I should have included the code for my own attempt, at #1 which is just as you suggest: update item set itemname = 'fox'; I've tried single, and double quoting the table and field names; call caps to the UPDATE etc, exactly matching the