Re: [GENERAL] Storing files: 2.3TBytes, 17M file count
On Mon, 28 Nov 2016 15:28:28 +0100 Thomas Güttler <guettl...@thomas-guettler.de> wrote: > Hi, > > Up to now, we don't store files in PostgreSQL. > > I was told, that you must not do this But this was 20 years ago. > > > I have 2.3TBytes of files. File count is 17M > > Up to now we use rsync (via rsnapshot) to backup our data. > > But it takes longer and longer for rsync to detect > the changes. Rsync checks many files. But daily only > very few files really change. More than 99.9% don't. > > Since we already store our structured data in postgres, I think > about storing the files in PostgreSQL, too. > > What is the current state of the art? > > Is it feasible to store file in PostgreSQL? Yes and no, it's another level of indirection, slower than pure filesystem solution. Rsync checks last read/access time, last write/modification time and file hash before copying it. If no one of those metadata change, rsync don't copy it. File hash must be recalculated if access time and modification time change. > Are there already projects which use PostgreSQL as storage backend? > > I have the hope, that it would be easier to backup only the files > which changed. Rsync tries to backup only the files that changed. There are other tools like cpdup, don't know if it's ported to linux, It's similar to rsync. You can use a p2p system, unlike ftp, rsync, etc they store a full Tree Hash (Tiger Tree Hash often) of file content to allow multi peer to peer copy. > Regards, > Thomas Güttler > > > Related question at rsnapshot mailing list: > https://sourceforge.net/p/rsnapshot/mailman/rsnapshot-discuss/thread/57a1a2f3.5090...@thomas-guettler.de/ > -- > Thomas Guettler http://www.thomas-guettler.de/ > --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres MultiXact 9.3 corruption resolved but advice needed
On Sat, 15 Oct 2016 18:40:39 + Yvonne Murphy <ymur...@vmware.com> wrote: > Our application runs against postgres 9.3.9.0-2921310. We recently > run into the dreaded ERROR: MultiXactId 82578299 has not been > created yet -- apparent wraparound. We've truncated this big table. > Will truncating save us from getting this error soon again or does > this bug have nothing to do with the size of the table? The > application has also been tested against. 9.3.12 will upgrading to > this reduce the chance of us hitting the bug until we can get the > application tested in 9.5. thanks > It's fixed in 9.3.14 https://www.postgresql.org/docs/9.3/static/release-9-3-14.html Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew Gierth, Álvaro Herrera) The usual symptom of this bug is errors like "MultiXactId NNN has not been created yet -- apparent wraparound". --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Wed, 31 Aug 2016 17:33:18 +0200 Nicolas Grilly <nico...@vocationcity.com> wrote: > Eduardo Morras wrote: > > > > Check BRIN indexs, they are "designed for handling very large > > tables in which certain columns have some natural correlation with > > their physical location within the table", I think they fit your > > needs. > > > Yes, a BRIN index on the tenant ID would be very useful if the rows > in the heap were naturally sorted by the tenant ID, but they are not. > They are naturally sorted by their order of insertion, which is > completely unrelated. The first step in solving this is to find a way > to keep rows belonging to the same tenant close to each other. The > second step could be to use a BRIN index. Then you can make multiple column partial indexes: CREATE INDEX CONCURRENTLY tenant_01_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id = 1; CREATE INDEX CONCURRENTLY tenant_02_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id = 2; This way each index has the data for a tenant, is updated only when the data for that tenant is updated and each index has it own files and you can reindex to clean index content and debloat. REINDEX INDEX tenant_01_idx; Or grouping them if there are too much indexes: CREATE INDEX CONCURRENTLY tenant_01_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id <= 300; CREATE INDEX CONCURRENTLY tenant_02_idx ON big_tenant_table (the_columns_with_data_you_need, tenant_id) WHERE tenant_id > 300 AND tenant_id <= 600; --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?
On Tue, 30 Aug 2016 13:12:33 +0200 Nicolas Grilly <nico...@gardentechno.com> wrote: > Hello, > > We are developing a multitenant application which is currently based > on MySQL, but we're thinking of migrating to PostgreSQL. > > We rely on clustered indexes to preserve data locality for each > tenant. Primary keys start with the tenant ID. This way, rows > belonging to the same tenant are stored next to each other. Because > all requests hit only one tenant, this is a great performance > improvement. > > PostgreSQL doesn't have clustered indexes ? I'm aware of the CLUSTER > command but it's a one-time operation ? and I'm wondering if this can > be a problem or not. > > Let's say we have a table containing data for 10,000 tenants and > 10,000 rows per tenant, for a total of 100,000,000 rows. Let's say > each 8 KB block contains ~10 rows. Let's way we want to compute the > sum of an integer column for all rows belonging to a given tenant ID. > > In MySQL/InnoDB, rows are stored in the leaf nodes of a B-tree. To > compute the sum, MySQL has to read at least 1,000 blocks (each block > containing ~10 rows). I deliberately neglect the cost of walking the > B-tree intermediate nodes. > > By comparison, PostgreSQL has to read at least 10,000 blocks (each > block containing ~10 rows, but most of the time, only one row will > match the tenant ID, other rows belonging to other tenants). > > A few questions: > > - Am I missing something? > - Am I overestimating the benefit of a clustered index in our case, > and the cost of not having one in PostgreSQL? > - Is there another technical solution to this problem? Check BRIN indexs, they are "designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table", I think they fit your needs. (see https://www.postgresql.org/docs/current/static/brin-intro.html) > Thanks, > Nicolas Grilly > Managing Partner > +33 6 03 00 25 34 > www.vocationcity.com ? Recruitment & application tracking software > www.gardentechno.com ? Consulting & software engineering --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replica Master-Salve Config.
On Fri, 5 Aug 2016 12:43:43 -0700 John R Pierce <pie...@hogranch.com> wrote: > On 8/4/2016 9:15 AM, Eduardo Morras wrote: > > If you set max_connections too high, those connections will > > compete/figth for same resources, CPU processing, I/O to disks, > > Memory and caches, Locks, and postgres will spend more time > > managing the resources than doing real work. Believe me (or us) set > > it as we say and use a bouncer like pgbouncer. It can run on the > > same server. > > idle connections only use a small amount of memory, a process, a > socket, and some file handles.when you have multiple databases, > its impossible to share a connection pool across them. > > the OP is talking about having 350 'tenants' each with their own > database and user on a single server. No, I was answering to Periko, the mail you cite is from Moreno Andreo, which I c and indent here: > Il 04/08/2016 18:15, Eduardo Morras ha scritto: >> [...] >> a) As others said, max_connections = 200 is too high. Set it at your >> number of cores (I use number of cores -1) > Excuse me for crossthreading, but I have to make things clearer to me. > That's one of the things I feel hard to understand how to approach in > my architecture. > My server has 350 DB with 350 users, everyone with its DB. Every user > has a "persistent" connection (used to replicate with rubyrep) and > some "burst" connections to connect to colleagues DB when necessary. > I'm going to split it across 2 servers, but it doesn't change things. > Even with pgbouncer, how can I manage having all these replication > connections (plus "burst" connections) with pgbouncer and a low > (<100) max_connections? Is it even possible? > Thanks > Moreno. He asks for a different scenario, with multiuser & multidatabase. > your 1 connection per core suggestion is ludicrious for this > scenario. in many database applications, most connections are > idle most of the time. sure you don't want much over about 2-4X > your cpu thread count actually active doing queries at the same time > if you want the max transaction/second aggregate throughput, but you > can still get acceptable performance several times higher than that, > depending on the workload, in my benchmarks the aggregate TPS rolls > off fairly slowly for quite a ways past the 2-4 connections per > hardware thread or core level, at least doing simple OLTP stuff on a > high concurrency storage system (lots of fast disks in raid10) Yes, for this scenario, where multiple users/apps has its own database (A*(1:1)) or various (A*(1:D)) you are right. The OP, Periko, asked for a pentaho setup of type (A*(C:1)) where pg_bouncer could help and OLAP queries, which have cpu/io bottleneck. A*(C:D) A = number of users/clients/Apps C = number of Connections per A D = number of Databases in server a = small A value (lower than a "normal" threshold/value) c = small C value (lower than a "normal" threshold/value) d = small D value (lower than a "normal" threshold/value) > -- > john r pierce, recycling bits in santa cruz --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming Replica Master-Salve Config.
On Thu, 4 Aug 2016 08:35:37 -0700 Periko Support <pheriko.supp...@gmail.com> wrote: > Hi. > > I'm testing streaming replica with ubuntu 14 and psql9.3. > > I'm trying to understand how this works, my plan is to setup the > slave server for pentaho reports for queries only. > > Now, my master server it has 128GB max_connections = 200 maybe I > will add more. shared_memory=18GB. > > My slave server doesn't have that resources, is a Xeon with 32GB of > RAM with psql same version > running on ubuntu14. > > My questions, does the slave server need to have the same resources? > 128GB and the same settings in specific max_connections and > shared_memory? > > Any comment appreciated, thanks!!! a) As others said, max_connections = 200 is too high. Set it at your number of cores (I use number of cores -1) b) Use pgbouncer as middleware between your apps and the master. c) Upgrade postgres to current 9.5. d) Are you sure pentaho reports queries aren't time and recurse hungry? They are (afaik) OLAP queries not OLTP. Perhaps you should switch the master with the slave. e) To answer your question, no, slave don't need to have the same resources or settings. f) For slave, if pentaho queries are OLAP, increase the work_mem setting. g) Do some metrics, check, more metrics, recheck, (by metrics I want to say to measure performance, calculate statistics and compare results). h) Read the documentation and wiki chapters on these topics. i) Ask. If you set max_connections too high, those connections will compete/figth for same resources, CPU processing, I/O to disks, Memory and caches, Locks, and postgres will spend more time managing the resources than doing real work. Believe me (or us) set it as we say and use a bouncer like pgbouncer. It can run on the same server. > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Online DW
On Sat, 11 Jun 2016 09:59:59 +0530 Sridhar N Bamandlapally <sridhar@gmail.com> wrote: > Ok, let me put this way, > > I need every transaction coming from application sync with both > production and archive db, > but the transactions I do to clean old data(before 7 days) on > production db in daily maintenance window should not sync with > archive db, > > Archive db need read-only, used for maintaining integrity with other > business applications > > Issue here is, > 1. etl is scheduler, cannot run on every transaction, even if it > does, its expensive > > 2. Materialize view(refresh on commit) or slony, will also sync > clean-up transactions > > 3. Replication is not archive, definitely not option > > I say, every online archive db is use case for this. As I see, you have 2 options (A, and B) A) With FDW 1) Create 2 databases on production: a) first, a normal database for production, with 7 last days data, b) second, as postgres_fdw remote database on archive server. https://www.postgresql.org/docs/current/static/postgres-fdw.html 2) Make a trigger on each table in production database to replicate inserts, updates and deletes to fdw remote tables. 3) At your scheduled time, truncate tables (don't delete content or the trigger will fire up) on your production db. Note, make date part of your primary keys, if not, the same pk may be reused on production tables and the insert on archive fdw tables will fail. B) Use pgpoolII, use replication to store the same data on both databases. On production db, delete old data. Don't connect both databases with streaming replication nor slony or anything else, pgpoolII will do the work. http://pgpool.net/mediawiki/index.php/Main_Page http://www.pgpool.net/docs/latest/tutorial-en.html#replication-config Read full documentation, above url cites point directly to the replication part but read full documentation. HTH > Thanks > Sridhar > Opentext --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Online DW
On Sat, 11 Jun 2016 09:59:59 +0530 Sridhar N Bamandlapally <sridhar@gmail.com> wrote: > Ok, let me put this way, > > I need every transaction coming from application sync with both > production and archive db, > but the transactions I do to clean old data(before 7 days) on > production db in daily maintenance window should not sync with > archive db, > > Archive db need read-only, used for maintaining integrity with other > business applications > > Issue here is, > 1. etl is scheduler, cannot run on every transaction, even if it > does, its expensive > > 2. Materialize view(refresh on commit) or slony, will also sync > clean-up transactions > > 3. Replication is not archive, definitely not option > > I say, every online archive db is use case for this. As I see, you have 2 options (A, and B) A) With FDW 1) Create 2 databases on production: a) first, a normal database for production, with 7 last days data, b) second, as postgres_fdw remote database on archive server. https://www.postgresql.org/docs/current/static/postgres-fdw.html 2) Make a trigger on each table in production database to replicate inserts, updates and deletes to fdw remote tables. 3) At your scheduled time, truncate tables (don't delete content or the trigger will fire up) on your production db. Note, make date part of your primary keys, if not, the same pk may be reused on production tables and the insert on archive fdw tables will fail. B) Use pgpoolII, use replication to store the same data on both databases. On production db, delete old data. Don't connect both databases with streaming replication nor slony or anything else, pgpoolII will do the work. http://pgpool.net/mediawiki/index.php/Main_Page http://www.pgpool.net/docs/latest/tutorial-en.html#replication-config Read full documentation, above url cites point directly to the replication part but read full documentation. HTH > Thanks > Sridhar > Opentext --- --- Eduardo Morras <emorr...@yahoo.es> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ever increasing pg_clog disk usage v8.4
On Mon, 1 Dec 2014 01:34:14 +0100 Powrie, William wpow...@indracompany.com wrote: Hello, I have a simple database with just a few tables that runs on an embedded Linux system 2.6.31.8. The OS including postresql 8.4 is loaded directly from cf-flash media and is not saved in any way across power recycles. It is always created at startup from the /inittab/rc script and nearly all the elements are inserted at this point. The database job is to service remote access requests via lighttpd from a web browser and provide an up to date account of the monitored equipment. The database is used to store hardware parameter values that are frequency polled via a number of Linux daemons. In normal operation there are no inserts but frequent updates and reads. Reliability is of upmost importance since each system is unmanaged but is remotely monitored. There are a number of systems currently deployed worldwide. Postgresql itself runs from a 56Meg ramdisk so disk space is limited. This is where the problem is. The files in pg_clog increase on a day to day basis until the ramdisk reaches 100% utilization. This takes roughly 30 days to occur and postgresql fails at this point. Is PostgreSQL 8.4 updated to last version? I think it was 8.4.22. pg_clog stores information about current commit status for open/active transactions. Do you close all transactions after they're work is done? Do you have any in IDLE in transaction state? The software runs vacuumdb from cron every day at a predefined time but this does not appear to do anything. I have increased it to run more frequently but this does not have the desired effect. Performing a full vacuum is not possible since I cannot easily get database exclusive access for which a full vacuum appears to need. Vacuum won't work because pg_clog stores data for open/active transactions, as they are alive (have a XID) that information must be available for new transactions. I have tried modifying the following vacuum parameters vacuum_freeze_table_age vacuum_freeze_min_age with no effect. If I run a vacuumdb analyse in verbose mode, this is an extract of what I see: INFO: vacuuming public.mib_snmpinteger INFO: index mib_snmpinteger_element_id_key now contains 2880 row versions in 23 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. I never see any index pages being returned to the operating system which is the problem Does anyone know how I can reclaim the every growing ramdisk space? It may be a bug corrected in newer 8.4 versions, update. A fast hack will be close the application connected to PostgreSQL, it will close the transactions. Better, rewrite your app to open/close transactions, using BEGIN/END. Regards, As final note, why don't use Sqlite3 for embedded instead PostgreSQL? William Powrie --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large table
On Mon, 22 Sep 2014 12:15:27 -0700 Luke Coldiron lukecoldi...@hotmail.com wrote: I'd guess that some other process held a transaction open for a couple of week, and that prevented any vacuuming from taking place. Interesting idea, on the surface I'm not sure how this would have happened in the system but I can certainly explore forcing this to happen and see if the result is similar. It happened when I developed with Java+Hibernate. It opened a transaction and made a lot of inserts and deletes while the app run. It created GB size tables with few rows and a permament 'IDLE in TRANSACTION' stops any autovacuum. --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump slower than pg_restore
On Thu, 03 Jul 2014 10:04:12 -0700 David Wall d.w...@computer.org wrote: I'm running PG 9.3.4 on CentOS 6.4 and noted that backing up my database takes much longer than restoring it. That seems counter-intuitive to me because it seems like reading from a database should generally be faster than writing to it. I have a database that pg_database_size reports as 18GB, and resulting dump is about 13GB in 27 files (split creates them as 512MB). A pg_dump backup -- with most of the data stored as large objects -- takes about 5 hours. But restoring that dump takes about 2 hours. So it's taking 2.5 times longer to back it up than to restore it. My backup script runs vacuumlo, then vacuum, then analyze, then pg_dump --format=c --oids $DB I actually push pg_dump output through gzip, gpg and split on 512MB files, but they shouldn't matter too much I figure as I have to run cat, gpg and gunzip before pg_restore. In fact, my restore should have been at a disadvantage because I used '-v' and showed the results to my ssh term over the Internet which includes a line for each LOID, and the postgresql.conf had 'ddl' logging on (which I suspect I can turn off in future restores to speed things up a bit). Is there something that might be wrong about my configuration that the backup is slower than the restore? No, there's nothing wrong. All transparent compressed objects stored in database, toast, lo, etc.. is transparently decompressed while pg_dump access them and then you gzip it again. I don't know why it doesn't dump the compressed data directly. Thanks, David --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading from 9.2 to 9.3 causes performance degradation
On Sun, 23 Mar 2014 20:58:03 -0300 (BRT) Álvaro Nunes Lemos Melo al_nu...@atua.com.br wrote: Hi, Recently, I've been trough a datacenter migration, and in this operation I'd also upgraded my PostgreSQL version from 9.2 to 9.3. My new hardware is slightly better than the old one, but the PostgreSQL performance has shown degradation in the whole system. Trying to figure out what could be happening, I'd installed instances of both versions on both servers, and double checked all the configuration parameters on them. The 9.2 version results make sense, there's a minor upgrade in the performance on the new server, but 9.3 number are worst than 9.2 on both servers, and surprisingly, worst in the newest than in the old one. After some research, I tried to disable transparent hugepages on the new one, but it made no effect. I used and specific query to benchmark, but as I said before, the whole system is slower. Below is my data, and I really hope we can find what is happening, or I'll have to downgrade to 9.2 and wait for 9.4 release. Old Server: Ubuntu 10.04.4 LTS 2.6.32-45-generic New Server: Debian GNU/Linux 7.3 (wheezy) 3.2.0-4-amd64 Different versions of Kernel, and IIRC some Linux vesions on 3.x has performance problems with PostgreSQL. Some questions: What filesystems do you use? Did you compile PostgreSQL or installed a binary? Is some kind of debug mode on in new server? Does querys call plpgsql or other functions on external languages (non sql). I think you did it correctly but, did you measure the performance with warmup systems? (Filled Caches, Analyzed tables, Updated indexes, etc...) Query Execution Times (average time of three executions, in seconds) ++---+---+ | Server | 9.2 | 9.3 | ++---+---+ | Old| 129 | 216 | ++---+---+ | New| 118 | 275 | ++---+---+ Thanks, L --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High Level Committers Wanted
On Thu, 20 Mar 2014 09:14:29 +0900 Michael Paquier michael.paqu...@gmail.com wrote: You should be careful not to give ideas away. There may be enough funds to really create a high-tech-funky suit like that :) -- Michael Hackers has free time to spare in funky projects, Big Corps has free money to spare in funky threats ;) --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] upgrade from 9.2.x to 9.3 causes significant performance degradation
On Tue, 17 Sep 2013 09:19:29 -0700 Lonni J Friedman netll...@gmail.com wrote: Greetings, I'm running a PostgreSQL 9.3.0 cluster (1 master with two streaming replication hot standby slaves) on RHEL6-x86_64. Yesterday I upgraded from 9.2.4 to 9.3.0, and since the upgrade I'm seeing a significant performance degradation. PostgreSQL simply feels slower. Nothing other than the version of PostgreSQL changed yesterday. I used pg_upgrade to perform the upgrade, and ran the generated analyze_new_cluster.sh immediately afterwards, which completed successfully. Prior to the upgrade, I'd generally expect a load average of less than 2.00 on the master, and less than 1.00 on each of the slaves. Since the upgrade, the load average on the master has been in double digits (hitting 100.00 for a few minutes), and the slaves are consistently above 5.00. There are a few things that are jumping out at me as behaving differently since the upgrade. vmstat processes waiting for runtime counts have increased dramatically. Prior to the upgrade the process count would be consistently less than 10, however since upgrading it hovers between 40 60 at all times. /proc/interrupts Local timer interrupts has increased dramatically as well. It used to hover around 6000 and is now over 20k much of the time. However, I'm starting to suspect that they are both symptoms of the problem rather than the cause. At this point, I'm looking for guidance on how to debug this problem more effectively. Don't know what happens but: a) Does analyze_new_cluster.sh include a reindex? If not, indexs are useless because analyze statistics says so. b) Did you configure postgresql.conf on 9.3.0 for your server/load? Perhaps it has default install values. c) What does logs say? thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to compare the results of two queries?
On Tue, 17 Sep 2013 10:59:43 -0400
Juan Daniel Santana Rodés jdsant...@estudiantes.uci.cu wrote:
I've been studying and I found that there EXECUTE but to use it, first
you should have used PREPARE, and in this case the values of the
parameters are already made inquiries.
For example the execution of the function would be something like ...
select compare('select * from table1', 'select * from table2');
For this case the result is false, then the queries are executed on
different tables.
If you create a table with:
CREATE TABLE comp
(
result1 : hstore,
result2 : hstore
);
insert in it the rows from selects:
INSERT INTO comp(result1, result2)
(SELECT * FROM table1, SELECT * FROM table2);
Substitute (SELECT * FROM table, SELECT * FROM table2) with your queries, store
the result of the queries on table1 and table2 tables or use a WITH in the
INSERT.
you can get the differences between both queries using '-' hstore operator:
SELECT (result1 - result2) as LEFT, (result2 - result1) as RIGHT FROM comp;
Or simulating an equal instruction:
SELECT (COUNT(result1 - result2)+COUNT(result2 - result1)=0) FROM comp; -- Not
sure about this one because uses COUNT on a hstore data column.
Thanks in advance.
Best regards from Cuba.
--- ---
Eduardo Morras emorr...@yahoo.es
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Call for design: PostgreSQL mugs
On Wed, 04 Sep 2013 00:08:52 +0200
Andreas 'ads' Scherbaum adsm...@wars-nicht.de wrote:
PostgreSQL folks!
We are looking for the next big thing. Actually, it's a bit smaller: a
new design for mugs. So far we had big blue elephants, small blue
elephants, frosty elephants, white SQL code on black mugs ... Now it's
time to design something new.
What's in for you? Fame, of course - we will announce the designer of
the next mug along with the mugs itself. Plus 4 mugs for you and your
friends.
Do you have a cool idea? Please let us know. Either reply here or send
an email to pgeu-bo...@postgresql.org.
CREATE OR REFILL mug{
id_owner INTEGER PRIMARY KEY,
owner_name VARCHAR(40),
CONSTRAINT id_owner PRIMARY KEY (mug),
CONSTRAINT fk_content FOREIGN KEY (content)
REFERENCES content (content_id)
)WITH (
OIDS=FALSE
);
Thanks,
--- ---
Eduardo Morras emorr...@yahoo.es
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs
On Thu, 05 Sep 2013 23:10:20 +0200 Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Marc Balmer m...@msys.ch writes: My SQL is PostgreSQL PostgreSQL really is YeSQL! -- you can check http://yesql.org too A draw of a hat? Like in Little Prince/Le Petit Prince, a snake with Elephant Inside logo. --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Call for design: PostgreSQL mugs
On Wed, 04 Sep 2013 00:08:52 +0200 Andreas 'ads' Scherbaum adsm...@wars-nicht.de wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, white SQL code on black mugs ... Now it's time to design something new. What's in for you? Fame, of course - we will announce the designer of the next mug along with the mugs itself. Plus 4 mugs for you and your friends. Do you have a cool idea? Please let us know. Either reply here or send an email to pgeu-bo...@postgresql.org. A big yellow elephant? Perhaps with a nice smile? Oh it's already taken ;) Thanks, --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql, sql client and I/O exceptions
On Thu, 18 Jul 2013 10:20:07 -0400 Paul Maddock paul.madd...@spidasoftware.com wrote: I am having trouble with postgresql connection. I am using squirrel sql client to write sql code for data qc'ing. On many occasions this has happened. It closes my connection on my client and stops my sql transaction. There are roughly 3 million records in the database I am editing. Version of Postgresql: psql (PostgreSQL) 9.2.4 JDBC for squirrel sql client: postgresql-9.2-1003.jdbc4.jar -- *syslog* 276 Jul 18 09:10:08 test-minapps kernel: [173502.656976] postgres[15212]: segfault at 7f4c34e8a44e ip 7f4c02d13605 sp 7fff92ba69f0 error 4 in postgres[7f4c02c86000+54e000] 277 Jul 18 09:10:12 test-minapps lsassd[1136]: 0x7f1607fff700:Error code 40286 occurred during attempt 0 of a ldap search. Retrying. *postgresql log* * 2013-07-18 09:10:24 EDT LOG: terminating any other active server processes 2013-07-18 09:10:24 EDT WARNING: terminating connection because of crash of another server process 2013-07-18 09:10:24 EDT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. * ** ~20** 2013-07-18 09:10:26 EDT LOG: all server processes terminated; reinitializing 2013-07-18 09:10:28 EDT LOG: database system was interrupted; last known up at 2013-07-18 09:03:54 EDT 2013-07-18 09:10:28 EDT LOG: database system was not properly shut down; automatic recovery in progress 2013-07-18 09:10:28 EDT LOG: redo starts at 39/BBAE7D60 2013-07-18 09:10:29 EDT LOG: record with zero length at 39/BD17A7D8 2013-07-18 09:10:29 EDT LOG: redo done at 39/BD17A7A8 2013-07-18 09:10:29 EDT LOG: last completed transaction was at log time 2013-07-18 09:10:10.968978-04 2013-07-18 09:11:08 EDT LOG: database system is ready to accept connections * *squirrel sql log* Error: An I/O error occured while sending to the backend. SQLState: 08006 ErrorCode: 0 - I'm researching the issue myself, but if you might know the cause it would help me tremendously. If it's under Linux, check that Out of Memory Killer don't slain some postmaster process. Disable it and try again. -- Paul Anthony Maddock --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] new instance of postgres on windows
On Thu, 18 Jul 2013 09:17:34 -0700 John R Pierce pie...@hogranch.com wrote: which will break the existing install Throw Windows away defenestrating the server Feed a dvd with last FreeBSD or Linux distro or Unix U Want. Restart. Install from dvd. Install Postgres and whatever you want the times you want with the configuration you want. Run. --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication with Drop: could not open relation with OID
On Thu, 20 Jun 2013 08:58:35 -0700 (PDT) salah jubeh s_ju...@yahoo.com wrote: Hello, I have a database server which do a complex views calculation, the result of those views are shipped to another database servers via a simple replication tool which have a high client loads. The tool is creating a table, and indexes based on predefined conf., then drop the table that needs to be synched then rename the temporary tables. i.e. BEGIN; DROP TABLE IF EXISTS y; -- the table I want to replace it ALTER TABLE x RENAME TO y; -- x contains the data which synched from server (already created) ALTER INDEX . RENAME TO .; -- rename indexes COMMIT; In version 8.3 , 8.4, and 9.1, I get errors could not open relation with OID; However with version 9.2 every thing works fine, I tried to lock the table in access exclusive mode before dropping it i.e BEGIN; LOCK TABLE y IN ACCESS EXCLUSIVE MODE; DROP TABLE IF EXISTS y; -- the table I want to replace ALTER TABLE x RENAME TO y; -- x is the temporay table ALTER INDEX x_x_name_idx RENAME TO y_x_name_idx; -- rename indexes COMMIT; But I still get the same errors. I have seen this post http://dba.stackexchange.com/questions/16909/rotate-a-table-in-postgresql and I used the same strategy for testing. In version 9.2 I was not able at all to generate the error. In 8.3, 8.4, 9.1 I was able to generate the errors. Since the tables, I am creating are quite big (several millions of record) , I am using drop and rename to speed the creation. For small table sizes, this problem does not appear often, but in my case it pops up often because of the table size. Is there any way to solve this for the mensioned versions In sqlite, which don't have the wonderful features Postgres has, you can do that using a column with the data version. Create a View to the table that enforces current data version. Add new data using a new data version number. When you want to switch, update the view and delete old version data. You must add version column as the first index entry on the indexes you create. HTH Regards --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing small image files
Hi Nelson. I worked with images and Postgresql, and want to add some comments: On Thu, 9 May 2013 13:40:15 -0500 Nelson Green nelsongree...@gmail.com wrote: OK, this is kind of convoluted, but I got a couple of test cases that work for me. The steps to make the first one are below. First I took one of the photos and shrunk it real small using GIMP. If you want to manipulate images automatically, don't use GIMP, use ImageMagick(for shell scripts) or OpenCV(for C sourcecode) Then I manually converted that to a base64 encoded text file: /usr/bin/base64 test.jpg test.64 If you must to use the pg shell, perhaps coding Misa's function in other language (python f.ex.) allows you directly insert the bytea. A use hint: disable toast compression for that table, images are already compressed, you don't need to waste time with it. That outputs a base64 string that matches test.64. Outputting that to a file and then converting it back gives me my image: /usr/bin/base64 -d output.64 newtest.jpg Like I said, kind of crazy, but it satisfies me that my basic premise is doable. I'll still get one of the front-end developers to whip out some PHP just to be safe. Thanks to all! --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What's wrong with postgresql.org domain?
I get Godaddy's page saying it's free --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What's wrong with postgresql.org domain?
On Thu, 18 Apr 2013 20:35:37 +1000 Chris Angelico ros...@gmail.com wrote: That one's clearly fine. What about www.postgresql.org? It's possible you have a poisoned cache for just that one record. ChrisA I have clean all (dns, web cache, etc) and get: camibar% nslookup www.postgresql.org Server: 62.42.230.24 Address:62.42.230.24#53 Non-authoritative answer: www.postgresql.org canonical name = www.mirrors.postgresql.org. Name: www.mirrors.postgresql.org Address: 87.238.57.232 Name: www.mirrors.postgresql.org Address: 98.129.198.126 Name: www.mirrors.postgresql.org Address: 217.196.149.50 In 87.238.57.232 (Sweeden) 98.129.198.126(San Antonio,TX) and 217.196.149.50(Saltzburg) i get lighttpd default page. Perhaps a miss configuration on my ISPs dns. I'll try with home connection this afternoon. Thanks --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup advice
On Mon, 15 Apr 2013 19:54:15 -0700
Jeff Janes jeff.ja...@gmail.com wrote:
On Tue, Apr 9, 2013 at 3:05 AM, Eduardo Morras
emorr...@yahoo.esjavascript:_e({}, 'cvml', 'emorr...@yahoo.es');
wrote:
On Mon, 8 Apr 2013 10:40:16 -0500
Shaun Thomas stho...@optionshouse.com javascript:_e({}, 'cvml',
'stho...@optionshouse.com'); wrote:
Anyone else?
If his db has low inserts/updates/deletes he can use diff between pg_dumps
(with default -Fp) before compressing.
Most diff implementations will read the entirety of both files into
memory, so may not work well with 200GB of data, unless it is broken into a
large number of much smaller files.
open-vcdiff only reads one of the files into memory, but I couldn't really
figure out what happens memory-wise when you try to undo the resulting
patch, the documentation is a bit mysterious.
xdelta3 will work on streamed files of unlimited size, but it doesn't
work very well unless the files fit in memory, or have the analogous data
in the same order between the two files.
I use for my 12-13 GB dump files:
git diff -p 1.sql 2.sql diff.patch
It uses 4MB for firts phase and upto 140MB on last one and makes a patch file
that can be recovered with:
patch 1.sql diff.patch 2.sql
or using git apply.
A while ago I did some attempts to co-compress dump files, based on the
notion that the pg_dump text format does not have \n within records so it
is sortable as ordinary text, and that usually tables have their stable
columns, like a pk, near the beginning of the table and volatile columns
near the end, so that sorting the lines of several dump files together will
gather replicate or near-replicate lines together where ordinary
compression algorithms can work their magic. So if you tag each line with
its line number and which file it originally came from, then sort the lines
(skipping the tag), you get much better compression. But not nearly as
good as open-vcdiff, assuming you have the RAM to spare.
Using two dumps taken months apart on a slowly-changing database, it worked
fairly well:
cat 1.sql | pigz |wc -c
329833147
cat 2.sql | pigz |wc -c
353716759
cat 1.sql 2.sql | pigz |wc -c
683548147
sort -k2 (perl -lne 'print ${.}a\t$_' 1.sql) (perl -lne 'print
${.}b\t$_' 2.sql) | pigz |wc -c
436350774
A certain file could be recovered by, for example:
zcat group_compressed.gz |sort -n|perl -lne 's/^(\d+b\t)// and print'
2.sql2
Be careful, some z* utils decompress the whole file on /tmp (zdiff).
There all kinds of short-comings here, of course, it was just a quick and
dirty proof of concept.
A nice one !
For now I think storage is cheap enough for what I need to do to make this
not worth fleshing it out any more.
Cheers,
Jeff
--- ---
Eduardo Morras emorr...@yahoo.es
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup advice
On Mon, 8 Apr 2013 10:40:16 -0500 Shaun Thomas stho...@optionshouse.com wrote: Anyone else? If his db has low inserts/updates/deletes he can use diff between pg_dumps (with default -Fp) before compressing. --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%
On Tue, 2 Apr 2013 18:08:36 -0500 David Noel david.i.n...@gmail.com wrote: On 4/2/13, Kevin Grittner kgri...@ymail.com wrote: David Noel david.i.n...@gmail.com wrote: 'select * from pg_stat_activity' shows that the queries are not waiting, and are in the idle state. The process is idle or the process is running the query? If the latter, what do you mean when you say the queries ... are in the idle state? select * from pg_stat_activity returns a table containing a column labeled state. When the postgres process is at 100% utilization and the application has hung, this query returns the value idle in that field. When things are running properly, as they are for the moment now, the value is active. Take care becasue idle is OK, idle in transaction not. Some checks about FreeBSD 8.3 (I use the same but with geom) a) Perhaps process are waiting to I/O, do you take zfs snapshots? How often? It can limit your i/o performance. Check the output of #zpool iostat 5 b) Is the zpool ok? If one of the disks lags behind the others (because hardware errors) reconstructing the raidz should show what you say. Check the output of #zpool status when the cpu storm happens. c) If you do a simple #top -U postgres (or the user that executes your postgres server), what does the STATE column show? Check that to know the kernel state of the process. d) Do you use the standard values for zfs? Specially arc values. --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Gitorious
On Thu, 7 Mar 2013 23:39:15 +1030 (CST) Neanderthelle Jones e...@view.net.au wrote: Was thinking of installing Gitorious but it's weird. You run install as root, it downloads and installs programs, dunno which ones, seems you don't need to know, but including mysql, and then of all things it wants you to reboot the host, i.e. it needs a host dedicated to Gitorious. It seems you can't configure it until there's been an automatic install, possibly it downloads and runs code that you can't look at before running it. Maybe I've got that wrong. Don't know why you ask it here, I use fossil (http://fossil-scm.org) as dvcs. One app executable, One file data, simple and fast. Has anyone installed it and can you, pre-installation, configure it to use pgsql instead of mysql, which we don't want here? Is this something safe to install, or let install itself? Why mysql? Why so inflexibly committed to it? (Or isn't that so?) Should the intended Gitorious server be isolated from your network? Perhaps it should go to gitorious mail lists? Surely Gitorious use mysql SQL syntax and mysql tricks. Elle -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_Restore
On Mon, 21 Jan 2013 22:16:00 +0530 bhanu udaya udayabhanu1...@hotmail.com wrote: Hello All, Can we achieve this template or pg_Restore in less than 20 minutes time. Any more considerations. Kindly reply. Thanks and RegardsRadha Krishna From: udayabhanu1...@hotmail.com Perhaps you can use the ntfs snapshot feature. a) Create the db with the data you want b) Shutdown PostgreSql completly c) Go to data folder, properties, make a recover point d) Start PostgreSql f) Work with it g) Stop PostgreSql h) Go to data folder, properties, recover the previous version i) Go to d) Don't know exactly the name of the ntfs property, if i remember well it's recover point, but haven't used Windows for a while. Perhaps other folders should be set to recover. It's easy with this feature shoot your own feet (yes, in plural). If something wrong happens recover from the dump. HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] reducing number of ANDs speeds up query RESOLVED
On Wed, 16 Jan 2013 23:42:23 +0100 T. E. Lawrence t.e.lawre...@icloud.com wrote: On 15.01.2013, at 17:32, Jeff Janes jeff.ja...@gmail.com wrote: T.E., Fortunately in point releases from August 2012 (9.0.9, 9.1.5, etc.), the default server log settings will log both the cancel and the command triggering the cancel. So if you are running an up to date server, you can just look in the logs to see what is happening. Cheers, Jeff That's interesting, I'll check it. Thank you. And now the million dollars question, do you have any transaction in 'IDLE IN TRANSACTION' state? If yes, there's one possible problem. For example, we used (note the past) a message queue middleware which uses a postgres db for message passing, but it keeps the transaction in 'IDLE IN TRANSACTION' state, so if i do a select * from message_tbl i get 1-5 rows, but the tbl used (note again the past) several Gb of hd space because autovacuum couldn't clean. Of course once discovered the problem the middleware was changed by another (home-built) one which don't keeps the IDLE IN TRANSACTION state. T. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] reducing number of ANDs speeds up query
On Sat, 12 Jan 2013 02:47:26 +0100 T. E. Lawrence t.e.lawre...@icloud.com wrote: Hello, I have a pretty standard query with two tables: SELECT table_a.id FROM table_a a, table_b b WHERE ... AND ... AND b.value=...; With the last AND b.value=... the query is extremely slow (did not wait for it to end, but more than a minute), because the value column is not indexed (contains items longer than 8K). You can construct your own home made index, add a new column in table b, with the first 8-16 bytes/chars of b.value, use this column on your query and refine to a complete b.value. Don't forget tocCreate an index for it too. You can keep this column updated with a trigger. Perhaps you can use a partial index for b.value column, i never used that feature so documentation/others can point you how to do it. However the previous conditions WHERE ... AND ... AND should have already reduced the candidate rows to just a few (table_b contains over 50m rows). And indeed, removing the last AND b.value=... speeds the query to just a millisecond. Is there a way to instruct PostgreSQL to do first the initial WHERE ... AND ... AND and then the last AND b.value=... on the (very small) result? Thank you and kind regards, T. --- --- Eduardo Morras emorr...@yahoo.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UFS2 Snapshots and Postgres
Hi everyone,
I'm using FreeBSD 9 for Postgres and want to know if these actions
are safe for make a backup of the database:
a) call pg_start_backup('b1')
b) take an UFS2 snapshot of data files
c) call pg_stop_backup()
d) change to the snapshot dir and rsync/dd/dump/transfer it to backup
file server
Is it safe to call pg_start_backup('b1',true)?
Thanks in advance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UFS2 Snapshots and Postgres
At 15:16 25/06/2012, you wrote:
On 6/25/2012 7:35 AM, Eduardo Morras wrote:
Hi everyone,
I'm using FreeBSD 9 for Postgres and want to know if these actions
are safe for make a backup of the database:
a) call pg_start_backup('b1')
b) take an UFS2 snapshot of data files
c) call pg_stop_backup()
d) change to the snapshot dir and rsync/dd/dump/transfer it to
backup file server
Is it safe to call pg_start_backup('b1',true)?
Thanks in advance
Snapshots are safe (but will result in a roll-forward on restart)
IF AND ONLY IF the log data and database table spaces are all on the
same snapshotted volume.
IF THEY ARE NOT then it will probably work 95% of the time, and the
other 5% it will be unrecoverable. Be very, very careful -- the
snapshot must in fact snapshot ALL of the involved database volumes
(log data included!) at the same instant.
Even if i do a pg_start_backup()? I thought it set db data/ files in
a consistent state and puts in wal files the new transactions and
apply them when call pg_stop_backup().
I must do it other way then :(
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backing up through a database connection (not pg_dump)
At 01:05 27/03/2012, Tim Uckun wrote: Is there a way to backup a database or a cluster though a database connection? I mean I want to write some code that connects to the database remotely and then issues a backup command like it would issue any other SQL command. I realize the backups would need to reside on the database server. You can use a stored procedure with this pl http://plsh.projects.postgresql.org/ , like this: CREATE FUNCTION dump_db(text, text) RETURNS text AS ' #!/bin/sh pg_dump $1 $2 ' LANGUAGE plsh; Note that you must CREATE LANGUAGE first, $1 is db_name, $2 is file name and check for write permissions of $2. Be careful and check your security because any shell script will run as postgres user. Don't know if it works with 9.x+ versions of postgres. HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Don't Thread On Me (PostgreSQL related)
At 00:32 27/01/2012, you wrote: There are cases where intraquery parallelism would be helpful. As far as I understand it, PostgreSQL is the only major, solid (i.e. excluding MySQL) RDBMS which does not offer some sort of intraquery parallelism, and when running queries across very large databases, it might be helpful to be able to, say, scan different partitions simultaneously using different threads. So I think it is wrong to simply dismiss the need out of hand. The thing though is that I am not sure that where this need really comes to the fore, it is typical of single-server instances, and so this brings me to the bigger question. The question in my mind though is a more basic one: How should intraquery parallelism be handled? Is it something PostgreSQL needs to do or is it something that should be the work of an external project like Postgres-XC? Down the road is there value in merging the codebases, perhaps making stand-alone/data/coordination node a compile time option? I still don't think threads are the solution for this scenary. You can do intraquery parallelism with multiprocess easier and safer than with multithread. You launch a process with the whole query, it divide the work in chunks and assigns them to different process instead of threads. You can use shared resources for communicattion between process. When all work is done, they pass results to the original process and it join them. The principal advantage doing it with process is that if one of the child subprocess dies, it can be killed/slained and relaunched without any damage to the work of the other brothers, but if you use threads, the whole process and all the work done is lost. It's not the unique advantage of using process vs threads. Some years ago, one of the problems on multi socket servers was with the shared memory and communications between the sockets. The inter cpu speed was too much slow and latency too much high. Now, we have multi cpus in one socket and faster intersocket communications and this is not a problem anymore. Even better, the speed and latency communicating 2 or more servers (not sockets or cpus) is reaching levels where a postgresql could have a shared memory between them, for example using Hypertransport cards or modern FC, and it's easier, lot easier, launch a remote process than a remote thread. Obviously such is not a question that needs to be addressed now. We can wait until someone has something that is production-ready and relatively feature-complete before discussing merging projects. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Refine Form of My querry
At 11:11 29/12/2011, you wrote: Hi every1 how are u all??? Members i am new in postgres and want to work on pgrouting but i am facing some issue with it. Will u please help me??? I have loaded my line shapefile in pgadmin environment, which creates a table name 'Route', but when i made a query 'Create View' at it, it will show an error that Relation 'Route' doesn't exist. can any1 explains me what this error means?? and how can i remove this error?? I am sending my problems with attachment please have a look at this.. You have to do 4 steps in the order i say: 1) Read the answer you get from Merlin Moncure. 2) Read the error message you get on page 2 of your attachment. 3) If you don't see why the error is happening, go to step 1. 4) Next time don't send attachments in .docx or any other pay-per-view/pay-per-work format With best regards No Problem ;) Saqib Kazmi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
Sorry for delay, I wonder if the prioritisation could be done using nice - each backend is a separate process, so why not to do 'nice(10)' for low priority processes or something like that. Yes, to a limited degree you can prioritise queries using nice and ionice, but it's awkward because: - All queries run as `postgres' so you can't do per-user limiting very easily - The postmaster doesn't have a way to set the nice level and ionice level when it forks a backend, nor does the backend have any way to do it later. You can use your own user-defined C functions for this, though. Postmaster hasn't a way to modify the system nice or ionice, but it can send signals to child process. Sending a user signal to sleep completely the child (i/o and cpu) or before any i/o call can do the trick. Perhaps it's time to add a query scheduler or a more complete/complex one in postgres. -- Craig Ringer -- Si la vida te da la espalda, ¡tocale el culo! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] odbc_fdw
At 16:27 28/11/2011, you wrote: Hi there! I built the current PostgreSQL 9.1.1 sources under Ubuntu 11.04 (in a VMware under Win7). I followed the steps in this guide: www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source It seems to work (I can run the server and connect to it with PgAdmin). Now I'd like to integrate the ODBC_FDW extension in my installation. However, I don't really understand the steps described on the download page: pgxn.org/dist/odbc_fdw/0.1.0 Can anybody tell me how to build it? I'm a software developer myself but a Linux newbie... Thank you for your help! As a final note, be careful to install only one odbc driver. Some linux distros use iODBC and others use unixODBC but some apps install the one they use so you can get a fail system with both libraries punching for being the king. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostGIS in a commercial project
At 09:26 21/10/2011, Thomas Kellerer wrote: Hello, we are using PostgreSQL in our projects and would like to integrate PostGIS as well. Now PostGIS is licensed under the GPL and I wonder if we can use it in a commercial (customer specific) project then. The source code will not be made open source, but of course the customer will get the source code. Is it still OK to use the GPL licensed PostGIS in this case? Is that then considered a derivative work because the application will not work without PostGIS? If it's pure GPL, then postgresql is automagically relicenced to GPL, because postgresql allows relicencing and GPL force it to be GPL. Your source code must be in GPL too. Remember, it's a virus licence and has the same problem that Midas king had. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Searching for bare letters
At 01:25 02/10/2011, Reuven M. Lerner wrote: Hi, everyone. I'm working on a project on PostgreSQL 9.0 (soon to be upgraded to 9.1, given that we haven't yet launched). The project will involve numerous text fields containing English, Spanish, and Portuguese. Some of those text fields will be searchable by the user. That's easy enough to do; for our purposes, I was planning to use some combination of LIKE searches; the database is small enough that this doesn't take very much time, and we don't expect the number of searchable records (or columns within those records) to be all that large. The thing is, the people running the site want searches to work on what I'm calling (for lack of a better term) bare letters. That is, if the user searches for n, then the search should also match Spanish words containing ñ. I'm told by Spanish-speaking members of the team that this is how they would expect searches to work. However, when I just did a quick test using a UTF-8 encoded 9.0 database, I found that PostgreSQL didn't see the two characters as identical. (I must say, this is the behavior that I would have expected, had the Spanish-speaking team member not said anything on the subject.) So my question is whether I can somehow wrangle PostgreSQL into thinking that n and ñ are the same character for search purposes, or if I need to do something else -- use regexps, keep a naked, searchable version of each column alongside the native one, or something else entirely -- to get this to work. Any ideas? You can use perceptual hashing for that. There are multiple algorithms, some of them can be tuned for specific languages. See this documentation: http://en.wikipedia.org/wiki/Phonetic_algorithm for a general description, http://en.wikipedia.org/wiki/Soundex is the first one developed, very old, http://en.wikipedia.org/wiki/Metaphone is a family of several modern algorithms. Remember that they are hashing algorithms, some words can collide because they have the same pronunciation but write different. I remember that datapark search engine uses them with dictionaries. You can check it too. http://www.dataparksearch.org/ Thanks, Reuven HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres for OLAP data mining
At 16:12 30/09/2011, Dario Beraldi wrote: Hello, I'm looking for some information (guidelines, docs, tutorials, etc...) about using postgres for OLAP, data mining, data warehousing. The sort of questions I'm trying to answer are on the lines of - What tools/SQL commands are available? - How should postgres be configured? - How suitable is postgres for these tasks (compared to other databases)? I have done some Google search but I would appreciate any advice/hint from more experienced users. Check these tutorials: http://pgexperts.com/document.html?id=49 http://www.pgexperts.com/document.html?id=40 There are more at http://www.pgexperts.com/presentations.html YOu can check the presentations made on several pgcons and similar events http://www.pgcon.org/2011/schedule/index.en.html http://www.postgresopen.org/2011/home/ HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looking for a faster way to do that
At 18:18 26/09/2011, you wrote:
Eduardo Morras nec...@retena.com wrote:
At 08:04 25/09/2011, haman...@t-online.de wrote:
select * from items where regexp_matches(items.code,'(ABC) (DE1)
(any_substring)'){};
Hi Eduardo,
it is clear that scanning the table once with a list of matches will
outperform
rescanning the table for every string wanted. Now, my problem is
that the patterns are
dynamic as well. So if I could translate a table with one
column and a few thousand rows
into something like
regexp_matches(code,'string1|string2|.string2781')
would ideally be a performant query. Unfortunately I have no idea
how I could achieve this
transformation inside the database. Doing it externally fails,
because any single query cannot
be more than so few characters.
You can create a plsql function and pass a setof text that do it.
Sorry but instead saying you What Must You Type, WMYT(c), i prefer
the How Should You Do way, HSYD(c). Note that you can get the same
results using other approachs (f.ex. using FTS described in chapter 12)
Check this topics:
Function
Creation http://www.postgresql.org/docs/9.0/static/sql-createfunction.html
Tutorial about Function
Creation http://www.adderpit.com/practical-postgresql/x10374.htm
Hi,
I tried the pl/sql approach to convert the contents of that patterns
table into a regex.
Results: 40 seconds runtime for 9500 candidates and 815 patterns
718 seconds for the same set of 9500 candidates, but using 4000
patterns instead.
So it seems that I am reaching limits of pattern match
Perhaps calling the function twice with half the values go faster.
How do you call the function? EXECUTE or SELECT? If you use EXECUTE
then the prepared plan in a previous call is ignored and is usually
faster. Don't know if in your case it run faster but you can try it.
As for the fulltext index (and the underlying tsquery): this is an
exact match rather than prefix
match, so I would need to know match patterns in advance in order to
build the index
I am thinking about that anyway (because ABC1234 likely should not
match ABC123 pattern
in my context), but I would sort of prefer a system where I can
state the rules when I
see the data set, rather than having to pre-create an index.
Thanks for the tutorial link :)
It's the one i used time ago. A bit old but very good one.
It seems that the responses on my post give all sorts of input that
will help me on other
tasks
Regards
Wolfgang Hamann
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA
At 07:43 25/09/2011, Reuven M. Lerner wrote: Hi, everyone. Daniel Verite mailto:dan...@manitou-mail.orgdan...@manitou-mail.org wrote: It would thus appear that there's a slight edge for dumping bytea, but nothing super-amazing. Deleting, however, is still much faster with bytea than large objects. The problem you have is with compression/decompression on large objects. If you see at it's sizes, you get 680KB for large objects and 573MB for bytea. Postgresql needs to decompress them before the dump. Even worse, if your dump is compressed, postgres decompress each large object , dump it and recompress. For this test, switch off compression on large objects/toast. For long term, perhaps a request to postgresql hackers to directly dump the already compressed large objects. The toast maybe more difficult because there are not only big size columns, but any column whose* size is bigger than a threshold (don't remember now, 1-2KB or similar) * Is it whose the correct word? I hope i have expressed correctly. EFME -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looking for a faster way to do that
At 08:04 25/09/2011, haman...@t-online.de wrote:
select * from items where regexp_matches(items.code,'(ABC) (DE1)
(any_substring)'){};
Hi Eduardo,
it is clear that scanning the table once with a list of matches will
outperform
rescanning the table for every string wanted. Now, my problem is
that the patterns are
dynamic as well. So if I could translate a table with one
column and a few thousand rows
into something like
regexp_matches(code,'string1|string2|.string2781')
would ideally be a performant query. Unfortunately I have no idea
how I could achieve this
transformation inside the database. Doing it externally fails,
because any single query cannot
be more than so few characters.
You can create a plsql function and pass a setof text that do it.
Sorry but instead saying you What Must You Type, WMYT(c), i prefer
the How Should You Do way, HSYD(c). Note that you can get the same
results using other approachs (f.ex. using FTS described in chapter 12)
Check this topics:
Function
Creation http://www.postgresql.org/docs/9.0/static/sql-createfunction.html
Tutorial about Function
Creation http://www.adderpit.com/practical-postgresql/x10374.htm
HTH
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looking for a faster way to do that
At 14:12 23/09/2011, haman...@t-online.de wrote:
Eduardo Morras wrote:
You can try these, i doubt they will use any index but its a
different approach:
select * from items where
length(items.code)length(rtrim(items.code,'ABC'));
select * from items where strpos(items.code,'ABC')=0 or
strpos(items.code,'any_substring')=0;
Hi,
if I understand this right, it does not mean check if the string
appears at position 0
which could translate into an index query, but rather check if the
string appears anywhere
and then check if that is position 0, so the entire table is checked.
The second one yes, as it checks all patterns you want only one time
per row they only needs one table scan. The first one eliminates the
substring 'ABC' from the string, if the lengths of both strings are
equal, the substring 'ABC' wasn't in it. If they are different, the
trimmed string will be shorter.
explain analyze select items.num, wantcode from items, n where
strpos(code, wantcode) = 0;
Nested Loop (cost=167.14..196066.54 rows=39178 width=36) (actual
time=0.074..36639.312 rows=7832539 loops=1)
Join Filter: (strpos((inner.code)::text, outer.wantcode) = 0)
- Seq Scan on n (cost=0.00..14.15 rows=815 width=32) (actual
time=0.005..2.212 rows=815 loops=1)
- Materialize (cost=167.14..263.28 rows=9614 width=42)
(actual time=0.007..13.970 rows=9614 loops=815)
- Seq Scan on items (cost=0.00..167.14 rows=9614
width=42) (actual time=0.044..14.855 rows=9614 loops=1)
Total runtime: 46229.836 ms
The query ran much faster than the pattern query, however. This
seems to be the performance
of just searching for a plain string vs. initializing the regex
engine every time (for 815
queries in a test set)
It will do only one table scan while your original code will do one
for each substring you want to test. You can add more and more
substrings without too much cost. If you want to use the regex engine
instead the postgresql string funtions check the regexp_matches(), it
should be faster if you have 3000 substrings.
select * from items where regexp_matches(items.code,'(ABC) (DE1)
(any_substring)'){};
Regards
Wolfgang Hamann
HTH
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] looking for a faster way to do that
At 09:45 23/09/2011, haman...@t-online.de wrote: A single anchored query select * from items where code ~ '^ABC'; does indeed use the index to retrieve data. So I wonder whether there might be a different approach to this problem rather than pattern matching. I recall I had a similar problem before with a contacts column possibly containing one or more email addresses. Here searches would also be number of people times number of requests performance. I finally ended up with a @@ match (contrib/tsquery) and a supporting GIST index, but that only supports exact match, not prefix You can try these, i doubt they will use any index but its a different approach: select * from items where length(items.code)length(rtrim(items.code,'ABC')); select * from items where strpos(items.code,'ABC')=0 or strpos(items.code,'any_substring')=0; HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum as easily obtained locks
At 16:35 03/08/2011, Michael Graham wrote: Yeah it said it last ran yesterday (and is currently running now), but I did I notice in the log: 2011-08-02 19:43:35 BST ERROR: canceling autovacuum task 2011-08-02 19:43:35 BST CONTEXT: automatic vacuum of table traffic.public.logdata5queue Which is interesting if not particularly useful. While you are running a test, you could keep an eye on the log to see if you get any of those messages. I think that would indicate autovacuum could not get a lock. You can also watch pg_stat_activity during the test, current_query will show you what table is being vacuumed. I'm pretty certain the autovacuumer is running while the test is on going what I can't work out is under what circumstances it will be able to return unused space to the OS in when it can't. One question, while you run your tests, does IDLE IN TRANSACTION messages happen? If you run your tests with a permanent connection to database, the tables are locked and autovacuum cannot work. I saw this with hibernate a queue... don't remember now, that stores data in postgres without closing connection. HTH -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum as easily obtained locks
At 19:32 03/08/2011, you wrote: On 08/03/11 10:21 AM, Eduardo Morras wrote: One question, while you run your tests, does IDLE IN TRANSACTION messages happen? If you run your tests with a permanent connection to database, the tables are locked and autovacuum cannot work. its not that tables are locked, its that vacuum (auto or otherwise) can't clear any tuples newer than the oldest active transaction. Yes, this is what happened to me 2-3 years ago. Table had only 1-3 rows but its size is 80-90 Gb. Once the app quits, autovacuum cleaned the table and became 8KB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql Web Hosting
At 01:22 30/09/2009, Matt Friedman wrote: Hi, I'm trying to migrate a site to a new hosting company. The backend uses postgresql 8 and php. Anyone have thoughts on decent hosting companies for this sort of thing? I'm just looking at shared hosting as this isn't a resource intensive site. I use http://www.hub.org/ . They offer vps, web hosting and access to postgres 8.x and mysql databases. Thanks, Matt HTH Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL and master multi-slave replication
Hello: I'm currently building a Pg multiserver and have a question about the possibility of working with WAL in a multislave environment. I have few master servers (write only) and multiple slave servers (read only). I want to write the WAL files from masters in a central postgres and that the multiple slaves reads them from time to time and auto update. From time to time deleting the old entries. Can it be done? Can it work as expected? Any problems working this way? Should wal files be compressed or similar? I'm thinking in a table schema like this: CREATE TABLE WAL_Table ( create_date timestamp with time zone NOT NULL, wal bytea, CONSTRAINT idx1 PRIMARY KEY (create_date) ) Thanks in advance Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL and master multi-slave replication
At 19:05 24/06/2009, Alvaro Herrera wrote: Eduardo Morras wrote: Hi, I'm currently building a Pg multiserver and have a question about the possibility of working with WAL in a multislave environment. I have few master servers (write only) and multiple slave servers (read only). I want to write the WAL files from masters in a central postgres and that the multiple slaves reads them from time to time and auto update. Never heard of a multiserver. I assume you mean there's a bunch (zero or more) slaves for each master. Yes, there will be 3 masters recolleting data (doing updates, inserts and deletes) for now and 5 slaves where we will do the searches. The slaves must have all the data recollected by the 3 masters and the system must be easily upgradable, adding new masters and new slaves. there's a suite to handle this kind of thing using pg_standby, including cleanup of old logs; see https://projects.commandprompt.com/public/pitrtools Didn't know about this solution (PITR yes, but not pitrtools) i'm checking it. Mind you, the WAL files are not stored in a database but in raw files. I have never seen anyone advocating the use of a database to store them. Well, i was thinking in slaves servers doing selects to the central repository to retrieve the wals archives from timestamp to timestamp and processing them. Thanks Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL and master multi-slave replication
At 19:25 24/06/2009, you wrote: On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morrasemor...@s21sec.com wrote: Yes, there will be 3 masters recolleting data (doing updates, inserts and deletes) for now and 5 slaves where we will do the searches. The slaves must have all the data recollected by the 3 masters and the system must be easily upgradable, adding new masters and new slaves. You know you can't push WAL files from 1 server into a slave, right? No, i didn't know that. I read the page http://www.postgresql.org/docs/8.4/static/warm-standby.html and infer/understood that when talking about primary server it could be more than one. Thanks for the clarification. Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.2 Memory Tuning
At 08:56 16/06/2009, you wrote: Hi, I need to measure the memory used by postgres under various loads in my application.How to calculate the exact memory used by postgres for OS:Windows as well as linux ? Download ProcessXP from Microsoft (before it was from Sysinternals). There you will see some cool graphs with private memory and other useful info (page faults, I/O, etc...) Thanks, CM J -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
