Re: [GENERAL] BDR table level replication questions
On 15 May 2015 at 04:26, Dennis denn...@visi.com wrote: What am I missing? How are the steps different from setting database replication? Please show the log output from both nodes, and the contents of SELECT * FROM bdr.bdr_nodes and SELECT * FROM bdr.bdr_connections on each node. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [GENERAL] Restarting DB after moving to another drive
Hi Daniel: On Wed, May 13, 2015 at 8:06 PM, Daniel Begin jfd...@hotmail.com wrote: ... - I still have a lot to learn on database management (it was simpler on user's side!-) Yep, we all do, even if we've been using it since it was called Postgres. Fortunately, I have found that pg_dumpall could do the job (I did not have a problem with it, I just did not know about it!-). If you didn't know about it I'll urge you to take the manual and do a sequential reading ( maybe not in full, this would take a long time, but at least skim through all of it sequentially, it's full of very interesting info and it's very useful and when you hit a problem you'll probably know there is something for it and search for it ). For me the manual is one of the major points for using pg . pg_dumpall is a fundamental tool for backups, as it's the only one that dumps the global objects. Good luck. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] template_postgis issue
Dear Concern, When I am installing PostgreSQL version 9.1 with PostGIS 1.5 then it's creating template_postgis database by default. But when I am installing below PostgreSQL version 9.3 with PostGIS 2.1.7 postgresql-9.3.6-2-windows-x64 postgis-bundle-pg93x64-setup-2.1.7-1 And PostgreSQL version 9.4 with PostGIS 2.1.7 postgresql-9.4.1-3-windows-x64 postgis-bundle-pg94x64-setup-2.1.7-1 It's not creating template_postgis by default. Kindly confirm what's the problem is. Note: I am installing these on Windows 7 machine (64 bit) Regards, Sachin -- 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] ECPG SET CONNECTION
On 14.05.2015 19:35, Bruce Momjian wrote: On Fri, May 31, 2013 at 02:26:08PM +0200, Leif Jensen wrote: Hi guys. In the ECPG manual (including latest 9.1.9) about ECPG SQL SET CONNECTION connection name; it is stated that This is not thread-aware. When looking in the ecpg library code connect.c for ECPGsetconn( ... ), it looks very much like it is thread-aware if translated with the --enable-thread-safety option. What should I believe ? Can someone comment on this report from 2013? Sorry, it seems I missed this email. Yes, the code should be thread-aware, at least I don't know of any problems with it. It appears to me that the docs haven't been updated by the patch that made ecpg work with threads back in the day. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at gmail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- 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] Restarting DB after moving to another drive
I use this script, run nightly via crontab, on my small pgsql servers. it runs as the postgres user. #!/bin/bash /usr/pgsql-9.3/bin/pg_dumpall --globals-only | gzip /home2/backups/pgsql/pgdumpall.globals.`date +\%a`.sql.gz for db in $(psql -tc select datname from pg_database where not datistemplate); do \ pg_dump -Fc -f /home2/backups/pgsql/pgdump.$i.$(date +\%a).dump $db done this creates a globals-only backup and a seperate backup of each database, for each day of the week. -- john r pierce, recycling bits in santa cruz -- 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]
Sachin Srivastava wrote: How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use this is for 9.1 database. You cannot do that. Switch to file system backup, that is much faster. Yours, Laurenz Albe -- 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] template_postgis issue
Hi Sachin, 2015-05-15 11:35 GMT+02:00 Sachin Srivastava ssr.teleat...@gmail.com: Dear Concern, When I am installing PostgreSQL version 9.1 with PostGIS 1.5 then it's creating template_postgis database by default. But when I am installing below PostgreSQL version 9.3 with PostGIS 2.1.7 postgresql-9.3.6-2-windows-x64 postgis-bundle-pg93x64-setup-2.1.7-1 And PostgreSQL version 9.4 with PostGIS 2.1.7 postgresql-9.4.1-3-windows-x64 postgis-bundle-pg94x64-setup-2.1.7-1 It's not creating template_postgis by default. Kindly confirm what's the problem is. Note: I am installing these on Windows 7 machine (64 bit) If you are running PostgreSQL 9.1+ you don't need to bother with the template database, but with the EXTENSION mechanism as introduced from this release. Please consider the following link: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 In particular, consider this part: If you are running PostgreSQL 9.1+, we recommend you don't even bother with the template database and just use CREATE EXTENSION postgis in the database of your choosing or use PgAdmin Extensions install feature which we will cover in this tutorial. http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 Regards, -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
Re: [GENERAL]
On 05/15/2015 02:46 PM, Sachin Srivastava wrote: Hi, How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use this is for 9.1 database. IMHO, if has been introduced in 9.3, it is not in 9.1, unless you find some official backport. My database size is 820 GB and it’s taking 7 hours to complete. You need to provide some storage information and spécification. With (most) bare filesystem operation such as dd: how much IO rate do you get? Cheers. -- 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]
On Fri, May 15, 2015 at 8:54 PM, Mihamina Rakotomandimby mihamina.rakotomandi...@rktmb.org wrote: On 05/15/2015 02:46 PM, Sachin Srivastava wrote: How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use this is for 9.1 database. IMHO, if has been introduced in 9.3, it is not in 9.1, unless you find some official backport. To be more precise, pg_dump supports dump from servers down to 7.0, so you can do it: http://www.postgresql.org/docs/9.3/static/app-pgdump.html Now the output may not be compatible with a 9.1 server and may need manual editing. Also, be careful that 9.1 servers do not support synchronized snapshots for parallel jobs, hence you may finish with an inconsistent dump if your server has write activity during the dump. Btw, if you are running on 9.1.2, update to 9.1.15. You are missing 3 years worth of many bug fixes, some of them being critical. -- Michael -- 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] BDR table level replication questions
I think I spotted the problem today, I am missing a role on node2.
Is there a pointer to or can you provide list of steps to take for the manual
cleanup mentioned the log file. I am assuming I just need to remove the
relevant entries in the bdr tables just on node2 in my case. Is that correct?
On May 15, 2015, at 8:07 AM, Dennis denn...@visi.com wrote:
On node1:
apimgtdb=# SELECT * FROM bdr.bdr_nodes
apimgtdb-# ;
node_sysid | node_timeline | node_dboid | node_status | node_name |
node_local_dsn
| node_init_from_dsn
-+---++-+---+
--+
6145502501690488441 | 2 | 16424 | r | node1 |
port=5432 dbname=apimgtdb user=postgres password= host=10.0.0.101
|
(1 row)
apimgtdb=# SELECT * FROM bdr.bdr_connections
apimgtdb-# ;
conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid |
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional |
conn_dsn |
conn_apply_delay | conn_replication_sets
-+---++---+--+---++--
+--+
6145502501690488441 | 2 | 16424 | 0 |
0 | 0 | f | port=5432 dbn
ame=apimgtdb user=postgres password=X host=10.0.0.101 |
| {test_rep_set}
(1 row)
2015-05-15 05:47:47.070 PDT ERROR: No peer nodes or peer node count
unknown, cannot acquire DDL lock
2015-05-15 05:47:47.070 PDT HINT: BDR is probably still starting up, wait
a while
2015-05-15 05:47:47.070 PDT CONTEXT: SQL statement SECURITY LABEL FOR
bdr ON TABLE idn_oauth2_access_token IS '{ sets : [“test_rep_set] }'
PL/pgSQL function bdr.table_set_replication_sets(regclass,text[])
line 30 at EXECUTE statement
2015-05-15 05:47:47.070 PDT STATEMENT: select
bdr.table_set_replication_sets(’table1', ARRAY[’test_rep_set']);
On node2:
apimgtdb=# SELECT * FROM bdr.bdr_nodes;
node_sysid | node_timeline | node_dboid | node_status | node_name |
node_local_dsn
| node_init_from_dsn
-+---++-+---+-
+--
6148366974419236867 | 1 | 16386 | i | node2 |
port=5432 dbname=apimgtdb user=postgres password= host=10.0.0.102
| port=5432 dbname=apimgtdb user=postgres password= host=10.0.0.101
(1 row)
apimgtdb=# SELECT * FROM bdr.bdr_connections;
conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid |
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional |
conn_dsn |
conn_apply_delay | conn_replication_sets
-+---++---+--+---++---
--+--+
6148366974419236867 | 1 | 16386 | 0 |
0 | 0 | f | port=5432
dbname=apimgtdb user=postgres password= host=10.0.0.102 |
| {test_rep_set}
(1 row)
The following lines repeat in the logs on node2:
2015-05-15 09:04:25.874 EDT LOG: worker process: bdr db: apimgtdb (PID
16330) exited with exit code 1
2015-05-15 09:04:30.880 EDT LOG: starting background worker process bdr
db: apimgtdb
2015-05-15 09:04:30.915 EDT ERROR: previous init failed, manual cleanup
is required
2015-05-15 09:04:30.915 EDT DETAIL: Found bdr.bdr_nodes entry for bdr
(6148366974419236867,1,16386,) with state=i in remote bdr.bdr_nodes
2015-05-15 09:04:30.915 EDT HINT: Remove all replication identifiers and
slots corresponding to this node from the init target node then drop and
recreate this database and try again
2015-05-15 09:04:30.916 EDT LOG: worker process: bdr db: apimgtdb (PID
16338) exited with exit code 1
On May 15, 2015, at 2:30 AM, Craig Ringer cr...@2ndquadrant.com
mailto:cr...@2ndquadrant.com wrote:
SELECT * FROM bdr.bdr_connections
[GENERAL]
Hi, How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use this is for 9.1 database. My database size is 820 GB and it’s taking 7 hours to complete. *Postgres Version: 9.1.2* *PogtGIS: 1.5* Regards, *Sachin Srivastava* Assistant Technical Lead(Oracle/PostgreSQL)| TSG *Cyient* | www.cyient.com
R: [GENERAL] Index on integer or on string field
Hello Arthur! So, i read that btree-gin have got the ability to enforce uniqueness. If in this 10.millions long table i have, in index, 50 recurring values, i can leave the alphabetical field and change to btree-gin the index on it?! Thank you! Francesco Da: Arthur Silva [arthur...@gmail.com] Inviato: venerdì 15 maggio 2015 17.26 A: Job Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Index on integer or on string field You should probably experiment with a btree-gin index on those. Em 15/05/2015 12:22, Job j...@colliniconsulting.itmailto:j...@colliniconsulting.it escreveu: Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Thank you! Francesco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Index on integer or on string field
Are you saying your indexed field has only 50 distinct values? Seems a horrible candidate for an index. Might be good to partition on those fifty values but ten million records probably doesn't warrant that. Sent from my iPhone On May 15, 2015, at 9:34 AM, Job j...@colliniconsulting.it wrote: Hello Arthur! So, i read that btree-gin have got the ability to enforce uniqueness. If in this 10.millions long table i have, in index, 50 recurring values, i can leave the alphabetical field and change to btree-gin the index on it?! Thank you! Francesco Da: Arthur Silva [arthur...@gmail.com] Inviato: venerdì 15 maggio 2015 17.26 A: Job Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] Index on integer or on string field You should probably experiment with a btree-gin index on those. Em 15/05/2015 12:22, Job j...@colliniconsulting.it escreveu: Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Thank you! Francesco -- 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] BDR table level replication questions
On node1:
apimgtdb=# SELECT * FROM bdr.bdr_nodes
apimgtdb-# ;
node_sysid | node_timeline | node_dboid | node_status | node_name |
node_local_dsn
| node_init_from_dsn
-+---++-+---+
--+
6145502501690488441 | 2 | 16424 | r | node1 |
port=5432 dbname=apimgtdb user=postgres password= host=10.0.0.101
|
(1 row)
apimgtdb=# SELECT * FROM bdr.bdr_connections
apimgtdb-# ;
conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid |
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional |
conn_dsn |
conn_apply_delay | conn_replication_sets
-+---++---+--+---++--
+--+
6145502501690488441 | 2 | 16424 | 0 |
0 | 0 | f | port=5432 dbn
ame=apimgtdb user=postgres password=X host=10.0.0.101 | |
{test_rep_set}
(1 row)
2015-05-15 05:47:47.070 PDT ERROR: No peer nodes or peer node count
unknown, cannot acquire DDL lock
2015-05-15 05:47:47.070 PDT HINT: BDR is probably still starting up, wait a
while
2015-05-15 05:47:47.070 PDT CONTEXT: SQL statement SECURITY LABEL FOR bdr
ON TABLE idn_oauth2_access_token IS '{ sets : [“test_rep_set] }'
PL/pgSQL function bdr.table_set_replication_sets(regclass,text[]) line
30 at EXECUTE statement
2015-05-15 05:47:47.070 PDT STATEMENT: select
bdr.table_set_replication_sets(’table1', ARRAY[’test_rep_set']);
On node2:
apimgtdb=# SELECT * FROM bdr.bdr_nodes;
node_sysid | node_timeline | node_dboid | node_status | node_name |
node_local_dsn
| node_init_from_dsn
-+---++-+---+-
+--
6148366974419236867 | 1 | 16386 | i | node2 |
port=5432 dbname=apimgtdb user=postgres password= host=10.0.0.102
| port=5432 dbname=apimgtdb user=postgres password= host=10.0.0.101
(1 row)
apimgtdb=# SELECT * FROM bdr.bdr_connections;
conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid |
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional |
conn_dsn |
conn_apply_delay | conn_replication_sets
-+---++---+--+---++---
--+--+
6148366974419236867 | 1 | 16386 | 0 |
0 | 0 | f | port=5432
dbname=apimgtdb user=postgres password= host=10.0.0.102 |
| {test_rep_set}
(1 row)
The following lines repeat in the logs on node2:
2015-05-15 09:04:25.874 EDT LOG: worker process: bdr db: apimgtdb (PID
16330) exited with exit code 1
2015-05-15 09:04:30.880 EDT LOG: starting background worker process bdr
db: apimgtdb
2015-05-15 09:04:30.915 EDT ERROR: previous init failed, manual cleanup is
required
2015-05-15 09:04:30.915 EDT DETAIL: Found bdr.bdr_nodes entry for bdr
(6148366974419236867,1,16386,) with state=i in remote bdr.bdr_nodes
2015-05-15 09:04:30.915 EDT HINT: Remove all replication identifiers and
slots corresponding to this node from the init target node then drop and
recreate this database and try again
2015-05-15 09:04:30.916 EDT LOG: worker process: bdr db: apimgtdb (PID
16338) exited with exit code 1
On May 15, 2015, at 2:30 AM, Craig Ringer cr...@2ndquadrant.com wrote:
SELECT * FROM bdr.bdr_connections
[GENERAL] Index on integer or on string field
Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Thank you! Francesco -- 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] Restarting DB after moving to another drive
Bonjour Francisco. Skimming the documentation sequentially is a cleaver advice, especially since the doc is much of the time well done and exhaustive. Unfortunately, even if I actually did it about 1 year ago, it seems this specific item slipped out of my mind :-( About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres). Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actually uses pg_dump where the doc says Post-data items include definitions of indexes, triggers... I would not worry if the doc said that indexes are simply copied but it says includes definition of indexes. Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? If indexes are actually rebuilt, why should it be done that way? - There must be good reason! Best regards, Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte Sent: May-15-15 05:12 To: Daniel Begin Cc: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive Hi Daniel: On Wed, May 13, 2015 at 8:06 PM, Daniel Begin jfd...@hotmail.com wrote: ... - I still have a lot to learn on database management (it was simpler on user's side!-) Yep, we all do, even if we've been using it since it was called Postgres. Fortunately, I have found that pg_dumpall could do the job (I did not have a problem with it, I just did not know about it!-). If you didn't know about it I'll urge you to take the manual and do a sequential reading ( maybe not in full, this would take a long time, but at least skim through all of it sequentially, it's full of very interesting info and it's very useful and when you hit a problem you'll probably know there is something for it and search for it ). For me the manual is one of the major points for using pg . pg_dumpall is a fundamental tool for backups, as it's the only one that dumps the global objects. Good luck. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Restarting DB after moving to another drive
HI Daniel: On Fri, May 15, 2015 at 5:35 PM, Daniel Begin jfd...@hotmail.com wrote: Bonjour Francisco. Buenos dias. Skimming the documentation sequentially is a cleaver advice, especially since the doc is much of the time well done and exhaustive. Unfortunately, even if I actually did it about 1 year ago, it seems this specific item slipped out of my mind :-( Bad luck, you managed to get one of the most important commands, but it's solved now. About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres). Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... Much longer than this, especially if as you say below you have a lot of indexes. It's one of the reasons many of us do not use pg_dumpall for anything but global objects, but use something like the crontab which John R. Pierce posted ( I use a similar thing, but with an intermediate script with dumps critical databases more frequently, skips recreatable ( may be someone can confirm if that word is right ? I mean test things which can be created from scratch ( as they come from a script ) ) databases and keeps several numbered copies ). Doing it this ways insures we can restore on criticality order if it needs to be done ( I even move unmodified partitions to a 'historic schema, which gets dumped only after a change, which cuts my backups times to a tenth ) One thing. I do not know how you are restoring the database, but when doing this things we use a specially tuned postgresql.conf ( fsync off, minimal loging, lots of worrk mems and similar things, as we do not care about durability ( you can just rerun initdb and redo the restore, and there is only 1 session connected, the restoring one ). This cuts the restore times to easily a tenth, then after ending it we restart the server with the normal cong. It is a must when doing this short of things. My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actually uses pg_dump where the doc says Post-data items include definitions of indexes, triggers... I would not worry if the doc said that indexes are simply copied but it says includes definition of indexes. Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? If indexes are actually rebuilt, why should it be done that way? - There must be good reason! You are out of luck, and it has a reason. First, pg_dumps does not copy, it dumps. It's simpler behaviour ( text output ) just output a SQL script which recreates everything and inserts all the data ( normally using copy for speed, but it's the same as inserting ). It takes care of generating a fast script ( meaning it creates the tables, then inserts the data, then creates indexes and reactivates constraints, which is faster than defining everything and inserting with indexes and constraints actives ). The reason to do it in text mode is you can dump between different version and/or architectures, and also the dump is much smaller than the db, specially if you compress it ( I always do it, testing a bit you can always find a compresor with will lead to faster backups, as saved disk writing easily offsets compression times, specially in moder multicpu memory rich machines ). Bear in mind in many scenarios you backup a lot ( we dump some critical things hourly, even if we are using replication ) and restore nearly never, and prefer to use a couple days more for the restore than a couple hours of degraded performance every backup. This being said, if you have an 820G db ( I still do not know which size is this, I suppose it's $PGDATA footprint ) of important data ( it does not seem critical in availability, as you are taking days and still in bussiness ) and you are having these kind of problems to dump and restore and move directories in your OS, and do not know how much time it takes for backups, you have a problem. You should practice backup AND restore more, because your question indicates you MAY be backing up your data, but you have never restored a backup. Also, the text output format is really good for the global objects in pg_dumpall, but not so much for the normal databases. For this you should use the custom format, unless it is a really small db. The problem with it is it can only do a database per file, and needs pg_restore to be read ( I know those are minors ). The advantage is instead of generating a plain text dump it builds a kind of tar file with the definitions and data for every object clearly separated, so you can do partial restores or whatever thing you want ( in fact, without options and without connecting to the database pg_restore spits out the same text file that a text dump will generate ). If you had used this technique you could have restored your tables in order, or restored only the data and then reindexed
Re: [GENERAL] Index on integer or on string field
You should probably experiment with a btree-gin index on those. Em 15/05/2015 12:22, Job j...@colliniconsulting.it escreveu: Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Thank you! Francesco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Index on integer or on string field
Hello Francesco, You should probably set timing on, run an explain analyze, and use pgbadger to diagnose your performance issue. While it may be the case that comparison in the index might be slightly faster because of the modulo arithmetic, those in-memory operations are extremely fast and it is likely that the seek in that index is the fastest part of your query. And since you only have 50 distinct values the btree is probably extremely shallow and there will be very few comparisons anyway. I don't know much about your query but I suspect that the issue is that your index scan is not selective enough so Postgres needs to scan a lot from disk (which is *extremely* slow). If you want to improve the performance you should first try to make the query as selective as possible, and try to put an index on a more selective column of the WHERE clause. If you really must rely primarily on that column which has only 50 distinct values you can try periodically running a CLUSTER command on the table for that column index (doc: http://www.postgresql.org/docs/devel/static/sql-cluster.html), or partition the table (doc: http://www.postgresql.org/docs/devel/static/ddl-partitioning.html) so that the data you are scanning is close together on disk and you can get as much of it per IO operation as possible. *Will J. Dunn* *willjdunn.com http://willjdunn.com* On Fri, May 15, 2015 at 1:32 PM, Arthur Silva arthur...@gmail.com wrote: Yes that's my suggestion. Btree-Gin deals with lots of repeated values much better than the Btree index as repeated keys are only stored once. Em 15/05/2015 12:38, Job j...@colliniconsulting.it escreveu: Hello Arthur! So, i read that btree-gin have got the ability to enforce uniqueness. If in this 10.millions long table i have, in index, 50 recurring values, i can leave the alphabetical field and change to btree-gin the index on it?! Thank you! Francesco -- *Da:* Arthur Silva [arthur...@gmail.com] *Inviato:* venerdì 15 maggio 2015 17.26 *A:* Job *Cc:* pgsql-general@postgresql.org *Oggetto:* Re: [GENERAL] Index on integer or on string field You should probably experiment with a btree-gin index on those. Em 15/05/2015 12:22, Job j...@colliniconsulting.it escreveu: Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Thank you! Francesco -- 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] Index on integer or on string field
On Fri, May 15, 2015 at 9:18 AM, Job j...@colliniconsulting.it wrote: Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Have you considered using a multi-column index here? if there's a more selective field you could index along with your rather non-selective one that might work better. But it's hard to tell without looking at you database usage etc. -- 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_upgrade failing from 9.3 to 9.4 because template0 already exists
Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. Attached is a patch that implements this, and it should be backpatch to all versions. Excellent and agreed. Just looked through the patch and didn't do a full review, but it looks good to me. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] ECPG SET CONNECTION
On Fri, May 15, 2015 at 01:10:27PM +0200, Michael Meskes wrote: On 14.05.2015 19:35, Bruce Momjian wrote: On Fri, May 31, 2013 at 02:26:08PM +0200, Leif Jensen wrote: Hi guys. In the ECPG manual (including latest 9.1.9) about ECPG SQL SET CONNECTION connection name; it is stated that This is not thread-aware. When looking in the ecpg library code connect.c for ECPGsetconn( ... ), it looks very much like it is thread-aware if translated with the --enable-thread-safety option. What should I believe ? Can someone comment on this report from 2013? Sorry, it seems I missed this email. Yes, the code should be thread-aware, at least I don't know of any problems with it. It appears to me that the docs haven't been updated by the patch that made ecpg work with threads back in the day. Thanks. Is that the only doc line that needs adjustment? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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_upgrade failing from 9.3 to 9.4 because template0 already exists
On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote: Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. Attached is a patch that implements this, and it should be backpatch to all versions. Excellent and agreed. Just looked through the patch and didn't do a full review, but it looks good to me. OK, thanks. I will apply it all branches later today as it is a data loss bug. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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_upgrade failing from 9.3 to 9.4 because template0 already exists
On Fri, May 15, 2015 at 10:51:15AM -0400, Bruce Momjian wrote: On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote: Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. Attached is a patch that implements this, and it should be backpatch to all versions. Excellent and agreed. Just looked through the patch and didn't do a full review, but it looks good to me. OK, thanks. I will apply it all branches later today as it is a data loss bug. Patch applied back through 9.0. Thanks for the report and analysis. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Restarting DB after moving to another drive
Thank for that comprehensive response! And you are right about practicing restore, I never had to :-) However, I use pg_dump on a regular basis (custom format) but I did not know the difference between database/database cluster (and pg_dumpall) until I had to move everything because the PGDATA drive started overheating. Now that I better understand what is going on with backup/restore processes, and considering... - The time it is going to take to rebuild the whole cluster ; - That I am the only user of the database; - That everything was just fine with the database, except the temperature of the drive - And considering the initial concern of this tread was about bad copy of symbolic links with windows I will make another attempt to copy everything on another drive from windows, unless someone tells me it is not possible. - I will move my external tablespaces content back to pgdata and drop them for the time I copy the db to the new drive. - Doing so, I will get rid of the symbolic link (from tablespaces) from where originated the initial error message - Without symbolic links, I should be able to copy the db using standard windows commands. - Setting up the new drive's letter to the old one before restarting the db is easy -The whole process should take 12hours instead of a week. Hoping it makes sense and that I have not missed something important (again) Thank for your patience :-) Daniel -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte Sent: May-15-15 12:20 To: Daniel Begin Cc: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] Restarting DB after moving to another drive HI Daniel: On Fri, May 15, 2015 at 5:35 PM, Daniel Begin jfd...@hotmail.com wrote: Bonjour Francisco. Buenos dias. Skimming the documentation sequentially is a cleaver advice, especially since the doc is much of the time well done and exhaustive. Unfortunately, even if I actually did it about 1 year ago, it seems this specific item slipped out of my mind :-( Bad luck, you managed to get one of the most important commands, but it's solved now. About dump/restore operation, restoring the database cluster is running for 24hrs now (psql -f pgdumpallOutputfile postgres). Since it took 13hrs to dump the cluster, I begin to wonder how long it is going to take to restore it... Much longer than this, especially if as you say below you have a lot of indexes. It's one of the reasons many of us do not use pg_dumpall for anything but global objects, but use something like the crontab which John R. Pierce posted ( I use a similar thing, but with an intermediate script with dumps critical databases more frequently, skips recreatable ( may be someone can confirm if that word is right ? I mean test things which can be created from scratch ( as they come from a script ) ) databases and keeps several numbered copies ). Doing it this ways insures we can restore on criticality order if it needs to be done ( I even move unmodified partitions to a 'historic schema, which gets dumped only after a change, which cuts my backups times to a tenth ) One thing. I do not know how you are restoring the database, but when doing this things we use a specially tuned postgresql.conf ( fsync off, minimal loging, lots of worrk mems and similar things, as we do not care about durability ( you can just rerun initdb and redo the restore, and there is only 1 session connected, the restoring one ). This cuts the restore times to easily a tenth, then after ending it we restart the server with the normal cong. It is a must when doing this short of things. My main concern is about how the indexes are managed in dump/restore operations. I understand that pg_dumpall actually uses pg_dump where the doc says Post-data items include definitions of indexes, triggers... I would not worry if the doc said that indexes are simply copied but it says includes definition of indexes. Since some of the indexes took days to build... does someone could confirm indexes are rebuilt instead of copied? If indexes are actually rebuilt, why should it be done that way? - There must be good reason! You are out of luck, and it has a reason. First, pg_dumps does not copy, it dumps. It's simpler behaviour ( text output ) just output a SQL script which recreates everything and inserts all the data ( normally using copy for speed, but it's the same as inserting ). It takes care of generating a fast script ( meaning it creates the tables, then inserts the data, then creates indexes and reactivates constraints, which is faster than defining everything and inserting with indexes and constraints actives ). The reason to do it in text mode is you can dump between different version and/or architectures, and also the dump is much smaller than the db, specially if you compress it ( I always do it, testing a bit you can
Re: R: [GENERAL] Index on integer or on string field
Yes that's my suggestion. Btree-Gin deals with lots of repeated values much better than the Btree index as repeated keys are only stored once. Em 15/05/2015 12:38, Job j...@colliniconsulting.it escreveu: Hello Arthur! So, i read that btree-gin have got the ability to enforce uniqueness. If in this 10.millions long table i have, in index, 50 recurring values, i can leave the alphabetical field and change to btree-gin the index on it?! Thank you! Francesco -- *Da:* Arthur Silva [arthur...@gmail.com] *Inviato:* venerdì 15 maggio 2015 17.26 *A:* Job *Cc:* pgsql-general@postgresql.org *Oggetto:* Re: [GENERAL] Index on integer or on string field You should probably experiment with a btree-gin index on those. Em 15/05/2015 12:22, Job j...@colliniconsulting.it escreveu: Hello, i have a table of about 10 millions of records, with the index on a string field. Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload. The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names. Is index are integer and not characteral, performance are better and workload reduces? Is there any comparisons? Thank you! Francesco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Nested fields with Mongo FDW
Hello,
I recently installed a PostgreSQL server to this spec:
server v9.3.6
EnterpriseDB mongo_fdw vREL-4_0_0
libbson v1.1.5
mongo C driver v1.1.5
and Mongo is at 2.7.1. Mapping fields in Mongo documents, including _id,
has been successful, with the exception of nested fields. Assuming my Mongo
document looks like this:
{
_id : ObjectId(5550ebf18cf0280b5fec373a),
message: {
order_id: 15
}
}
I would think this table should do:
CREATE FOREIGN TABLE ats.messages (
_id NAME,
message.order_id INT
)
SERVER mongo_server OPTIONS (database 'db', collection 'test_collection')
While that doesn't raise any errors, any SELECT against the table shows
message.order_id as empty. Are nested fields supported in this version of
mongo_fdw? If so, what's the proper way to map nested fields to table
columns?
thank you,
MC
