Re: [GENERAL] I need to load mysql dump to postgres...
On Tue, 2011-10-11 at 22:37 -0400, unclebob wrote: > Is there a program which would just parse mysql dump file and load > data to postgresql using plain sql inserts? EDB has an open source and free tool for this: http://www.enterprisedb.com/migrationwizard-11 If you are using RPM based distros, there are also RPMs of this in http://yum.postgresql.org -- package name ise mysqlmigrator. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] [postgis-users] Query slow down, never completes
Yes, I did a version of the query where i terminated the connection in the
loop after one year, and then reconnected to the server for each year in the
query.
This did not change anything, and the query still halted on the same year.
Like this:
# For each year calculate the distance to border and insert into the
borddist table
yearlist = range(1946, 2009, 1)
for x in yearlist:
db1 = psycopg2.connect("host=192.168.1.186 dbname=priogrid user=postgres
password=postgres")
cur = db1.cursor()
print str(time.ctime())+ " Creating borddist for year "+str(x)+"."
cur.execute("INSERT INTO borddist(gid, gwcode, gridyear, borddist)
SELECT a.gid, a.gwcode, "+str(x)+", MIN(ST_Distance(ST_Transform(a.centroid,
954010), ST_Transform(b.geom, 954010)))/1000 AS borddist \n" \
"FROM priogridall_geom a, cshapes b, cshapes c WHERE
a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+"
\n" \
"and a.gwcode = c.gwcode and st_intersects(b.geom, c.geom)
AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")
db1.commit()
cur.close()
db1.close()
db1.commit()
print str(time.ctime())+ " Done"
cur.close()
db1.close()
I also followed your suggestion to not write any data. Just do a select,
without any select into or insert into.
The same problem occurred.
Script:
cur.execute("SELECT a.gid, a.gwcode, "+str(x)+", "\
"MIN(ST_Distance(a.centroid, b.geom)) "\
"FROM priogridall_geom a, cshapes b, cshapes c WHERE
a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+"
"\
"AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom)
AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")
db1.commit()
Thank you very much for looking into this. I have used over two weeks to try
to figure this out.
The only thing i can do is to run the script for 1/2 the years, then restart
the server "service postgresql restart" and then run it for the next 1/2.
Some memory and cpu information.
Here is how it looks in free -m and top when the script have halted.
total used free sharedbuffers cached
Mem: 5977 5371605 0139 4735
-/+ buffers/cache: 495 5481
Swap:6075 1 6074
top - 09:51:07 up 1 day, 18:44, 2 users, load average: 1.88, 1.32, 1.20
Tasks: 165 total, 2 running, 162 sleeping, 0 stopped, 1 zombie
Cpu(s): 53.2%us, 1.3%sy, 0.0%ni, 45.5%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 6120848k total, 5505868k used, 614980k free, 143004k buffers
Swap: 6221820k total, 1468k used, 6220352k free, 4849556k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
7810 postgres 20 0 2264m 1.0g 1.0g R 100 18.0 988:09.39 postgres
1417 andreas 20 0 163m 39m 14m S3 0.7 3:27.13 compiz
907 root 20 0 57092 34m 10m S2 0.6 1:48.73 Xorg
7088 andreas 20 0 93012 15m 10m S2 0.3 0:02.80 gnome-terminal
10772 andreas 20 0 204m 96m 23m S2 1.6 0:19.88 chromium-browse
1525 andreas 20 0 100m 15m 9548 S1 0.3 5:11.16 unity-panel-ser
10675 andreas 20 0 308m 50m 29m S1 0.8 0:07.55 chromium-browse
1088 root 20 0 9420 1632 964 S0 0.0 0:07.01 nmbd
1389 andreas 20 0 5552 2740 700 S0 0.0 1:17.77 dbus-daemon
10825 andreas 20 0 35836 19m 6276 S0 0.3 0:02.94 idle-python2.6
1 root 20 0 3040 1780 1220 S0 0.0 0:02.16 init
2 root 20 0 000 S0 0.0 0:00.01 kthreadd
3 root 20 0 000 S0 0.0 0:07.21 ksoftirqd/0
6 root RT 0 000 S0 0.0 0:00.00 migration/0
7 root RT 0 000 S0 0.0 0:00.00 migration/1
9 root 20 0 000 S0 0.0 0:03.00 ksoftirqd/1
11 root 0 -20 000 S0 0.0 0:00.00 cpuset
After i restart the postgresql service:
total used free sharedbuffers cached
Mem: 5977 4319 1657 0139 3687
-/+ buffers/cache: 492 5484
Swap: 6075 1 6074
top - 09:52:33 up 1 day, 18:46, 2 users, load average: 1.42, 1.33, 1.22
Tasks: 164 total, 1 running, 162 sleeping, 0 stopped, 1 zombie
Cpu(s): 1.3%us, 0.3%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 6120848k total, 4430204k used, 1690644k free, 143092k buffers
Swap: 6221820k total, 1468k used, 6220352k free, 3776880k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
907 root 20 0 52996 30m 6728 S1 0.5 1:49.78 Xorg
1417 andreas 20 0 163m 39m 14m S1 0.7 3:28.43 compiz
194 root 20 0 000 S0 0.0 0:48.68 usb-storage
1447 root 20 0 5564 1000 712 S0 0.0 0:23.28 udisks-daemon
1525
[GENERAL] How to make replica and use it when master is down ?
Hello everyone, from a few days I want to ask how to make replica server of my database and when spontaneously my master server going down due to loosing power or has been offline more then 5 min, replica server to become master server and accept all querys to database like select, insert, update and so on and when original master server become online to sync his db or just to stay slave / replica server until one of the servers does not going down ? Well, I don't know if this is possibly. -- Regards, Condor -- 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] Drill-downs and OLAP type data
Hi, On 12 October 2011 14:50, Anthony Presley wrote: > After a few weeks of searching around, we're running into dead-ends on the > front-end, and the back-end. PG doesn't support OLAP / MDX and the GUI > tools that do this, for the most part, require MDX / OLAP (SPSS and DB2, MS > SQL Analytics, etc...). > What's the PG route here? Are there some secrets / tips / tricks / contrib > modules for handling this? Our db has very simple star schema and several materialised tables for aggregates. We need OLAP style queries with OLTP performance :). After several upgrades we ended up with 16 cores, 80GB of RAM and fast SAN but performance wasn't good. Meanwhile we evaluated several NoSQL solutions and several comercial MPP solutions -- and the winner is Greenplum! (you can get CE version for free -- max two processors (but unlimited cores) and almost no HA options). AsterData's nCluster has very nice MapReduce integration but looks quite immature. I would say that roll-ups/drill-downs/cubes sound obsolete to me :) I suggest the following tools (list is not sorted by anything) - RapidMiner (http://rapid-i.com/content/view/181/196/) - Tableau (http://www.tableausoftware.com/public/gallery) - AlpineMiner (http://www.alpinedatalabs.com/, http://alpineminer.org/) - Jasper (http://www.jaspersoft.com/) - Pentaho (http://www.pentaho.com/) -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using constraint exclusion with 2 floats
Hi everyone, Is there an easy way (that I maybe missed) to use constraint exclusion with 2 floats ? Must I find an extension the temporal extension which create a new type with 2 timestamp or can I usethe && operator with 2 fields ? Thank you
[GENERAL] Conceptual Architecture
Hi Guys, We are new to PostgresSQL and in the process of learning. Could you please point us to a simple white paper/doc which describes the Conceptual Architecture of PostgresSQL? Thanks Sarma
[GENERAL] how to key/value iterate in stored function
I want to create a variable that is an array or list of key/value pairs. The key would be a table name and the value would be a sql statement or a value or list of values for which I could extract to create a sql statement and execute. I have tried experimenting with various arrays, but there is no clear example or documentation. The key and the value would be text. I want to iterate over the entire array (or list), so would need an example on that or some pointers there, if there is help. J.V. -- 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 key/value iterate in stored function
On Wed, Oct 12, 2011 at 6:46 AM, J.V. wrote: > I want to create a variable that is an array or list of key/value pairs. > > The key would be a table name and the value would be a sql statement or a > value or list of values for which I could extract to create a sql statement > and execute. > > I have tried experimenting with various arrays, but there is no clear > example or documentation. The key and the value would be text. > > I want to iterate over the entire array (or list), so would need an example > on that or some pointers there, if there is help. > Which version of PostgreSQl are you running? It's a little easier on 8.4 and higher (unnest comes in handy here), but prior to 1.4 you can loop from array_lower to array_upper. 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
[GENERAL] Monitoring Replication
Hello all, I use Nagios to monitor various things on a few servers and have recently set up a hot-standby server and would obviously like to include the state of streaming replication in my monitoring. I know about the pg_stat_replication view on the master and the pg_last_xlog_receive_location() system function on the standby... and while there is no traffic I know that the values from the sent_location column from the master view should match the value returned by pg_last_xlog_receive_location on the standby. I also assume that if streaming replication fails completely the pg_stat_replication view on the master should simply return no records... so that should be easy to detect. The confusion I have is how exactly can I determine just how far behind the replication is during loads? Currently with no traffic (servers not in production yet) sent_location on the master is "A/10018560" and pg_last_xlog_receive_location() on the standby also returns "A/10018560"... How far apart can these be for me to start worrying? I could make a bit more sense of all this if they were simple timestamps or something, but the hex values returned boggle my mind. Any advice on these issues or other tips on monitoring the replication would be greatly appreciated. Thanks, Brandon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Conceptual Architecture
Hi Guys, We are new to PostgresSQL and in the process of learning. Could you please point us to a simple white paper/doc which describes the Conceptual Architecture of PostgresSQL? Thanks Sarma
Re: [GENERAL] Conceptual Architecture
On Wed, Oct 12, 2011 at 1:50 AM, Sarma Chavali wrote: > Hi Guys, > > We are new to PostgresSQL and in the process of learning. > > Could you please point us to a simple white paper/doc which describes the > Conceptual Architecture of PostgresSQL? > I don't know what you mean by "Conceptual Architecture." Can you clarify? 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] how to key/value iterate in stored function
On Wed, Oct 12, 2011 at 8:46 AM, J.V. wrote:
> I want to create a variable that is an array or list of key/value pairs.
>
> The key would be a table name and the value would be a sql statement or a
> value or list of values for which I could extract to create a sql statement
> and execute.
>
> I have tried experimenting with various arrays, but there is no clear
> example or documentation. The key and the value would be text.
>
> I want to iterate over the entire array (or list), so would need an example
> on that or some pointers there, if there is help.
use thee the hstore!
postgres=# select * from each('a=>1,b=>2');
key | value
-+---
a | 1
b | 2
(2 rows)
(if you do stick with arrays, use unnest() -- it can be built for
older versions if you don't have it).
merlin
--
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] Conceptual Architecture
On 10/12/2011 3:50 AM, Sarma Chavali wrote: Hi Guys, We are new to PostgresSQL and in the process of learning. Could you please point us to a simple white paper/doc which describes the Conceptual Architecture of PostgresSQL? Thanks Sarma Ahh, odd question. Have you read the fine online manual? http://www.postgresql.org/docs/9.1/interactive/index.html Read about Relational Theory, and MVCC (ask google). Have you read Greg's book? http://www.2ndquadrant.com/books/ After that, read the source code. -Andy -- 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] Conceptual Architecture
On 10/12/11 4:08 AM, Sarma Chavali wrote: Hi Guys, We are new to PostgresSQL and in the process of learning. Could you please point us to a simple white paper/doc which describes the Conceptual Architecture of PostgresSQL? http://www.postgresql.org/docs/current/static/tutorial-arch.html its pretty much as simple as... client application --> libpq client library --> postgresql server unless you mean something more specific by Conceptual Architecture ? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Drill-downs and OLAP type data
2011/10/12 Ondrej Ivanič > Hi, > > On 12 October 2011 14:50, Anthony Presley wrote: > > After a few weeks of searching around, we're running into dead-ends on > the > > front-end, and the back-end. PG doesn't support OLAP / MDX and the > GUI > > tools that do this, for the most part, require MDX / OLAP (SPSS and DB2, > MS > > SQL Analytics, etc...). > > What's the PG route here? Are there some secrets / tips / tricks / > contrib > > modules for handling this? > > Our db has very simple star schema and several materialised tables for > aggregates. We need OLAP style queries with OLTP performance :). After > several upgrades we ended up with 16 cores, 80GB of RAM and fast SAN > but performance wasn't good. Meanwhile we evaluated several NoSQL > solutions and several comercial MPP solutions -- and the winner is > Greenplum! (you can get CE version for free -- max two processors (but > unlimited cores) and almost no HA options). AsterData's nCluster has > very nice MapReduce integration but looks quite immature. > The *problem* with Greenplum is that it's ultra-expensive once you leave the CE version - and you're not supposed to be using the CE version for commercial usage last I read the license. Has that changed? The last pricing I saw was around $16k per CPU (it may have been per core?). At that point, you're now in the realm of SQL Server Analysis and DB2 SPSS. > I would say that roll-ups/drill-downs/cubes sound obsolete to me :) I > suggest the following tools (list is not sorted by anything) > - RapidMiner (http://rapid-i.com/content/view/181/196/) > - Tableau (http://www.tableausoftware.com/public/gallery) > - AlpineMiner (http://www.alpinedatalabs.com/, http://alpineminer.org/) > - Jasper (http://www.jaspersoft.com/) > - Pentaho (http://www.pentaho.com/) > Thanks, but I'm well aware of all of these packages. Our problem is pairing up a web based GUI to a database we love using. Doesn't seem possible, because the user-friendly OLAP / data analysis / dashboard tools are all expecting functionality that PG doesn't have. It sounds like, we're either choosing a different DB to work with the pretty GUI tools, or writing a GUI tool to work with PG. -- Anthony Presley
Re: [GENERAL] Drill-downs and OLAP type data
2011/10/12 Filip Rembiałkowski > > Mondrian (which is a part of Pentaho BI stack) is an open source OLAP > engine with MDX. > See http://community.pentaho.com/projects/bi_platform/ Thanks ... we're familiar with Mondrian. Unfortunately, the only stable GUI for it under $30k is jPivot, which is basically dead since 2008. And, IMHO, it's a very ugly product. The newer jPivot (which Pentaho and Jasper have both "forked"), is somewhat an improvement. But now, we need a PG database to store the raw data in, and a Mondrian OLAP server to store the drill-downs in. ... Was hoping this wasn't going to be 30 tools to accomplish our needs. -- Anthony Presley
Re: [GENERAL] how to save primary key constraints
On Tue, Oct 11, 2011 at 6:37 PM, Chris Travers wrote: > On Tue, Oct 11, 2011 at 4:33 PM, Raymond O'Donnell wrote: >> On 12/10/2011 00:24, J.V. wrote: >>> pg_catalog table does not exist. >>> >> >> It's not a table, it's PostgreSQL's version of the information_schema >> catalog: >> >> http://www.postgresql.org/docs/8.4/static/catalogs.html >> > Not quite. PostgreSQL has an information_schema too. > > The pg_catalog is the schema of system catalogs for PostgreSQL. The > catalogs are not guaranteed to be stable interfaces the way the > information_schema is. This -- always look for your answer first in information_schema. As a bonus, it's also portable to many other databases and is much easier to follow. Only go to the catalogs if your performance requirements are extreme and/or you are looking for postgres specific info not found in the standard schema. merlin -- 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 key/value iterate in stored function
I tried hstore with no luck.
How do I initialize the array?
How do I loop through each key/value pair in a stored function to
retrieve the key and value for each item in the list?
I need a list of key value pairs. Any options there?
J.V.
On 10/12/2011 8:51 AM, Merlin Moncure wrote:
On Wed, Oct 12, 2011 at 8:46 AM, J.V. wrote:
I want to create a variable that is an array or list of key/value pairs.
The key would be a table name and the value would be a sql statement or a
value or list of values for which I could extract to create a sql statement
and execute.
I have tried experimenting with various arrays, but there is no clear
example or documentation. The key and the value would be text.
I want to iterate over the entire array (or list), so would need an example
on that or some pointers there, if there is help.
use thee the hstore!
postgres=# select * from each('a=>1,b=>2');
key | value
-+---
a | 1
b | 2
(2 rows)
(if you do stick with arrays, use unnest() -- it can be built for
older versions if you don't have it).
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to list or array of key value pairs
I need to rephrase this because of some confusion as to what I was looking for. I want to create and initialize a list or array of key/value pairs. Then I want to iterate through this list (or array) retrieving each key/value for use in a sql statement. I want to do this in a stored function. that's all any ideas? J.V. -- 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 key/value iterate in stored function
On Oct 12, 2011, at 14:43, "J.V." wrote:
> I tried hstore with no luck.
>
> How do I initialize the array?
>
> How do I loop through each key/value pair in a stored function to retrieve
> the key and value for each item in the list?
>
> I need a list of key value pairs. Any options there?
>
> J.V.
>
> On 10/12/2011 8:51 AM, Merlin Moncure wrote:
>> On Wed, Oct 12, 2011 at 8:46 AM, J.V. wrote:
>>> I want to create a variable that is an array or list of key/value pairs.
>>>
>>> The key would be a table name and the value would be a sql statement or a
>>> value or list of values for which I could extract to create a sql statement
>>> and execute.
>>>
>>> I have tried experimenting with various arrays, but there is no clear
>>> example or documentation. The key and the value would be text.
>>>
>>> I want to iterate over the entire array (or list), so would need an example
>>> on that or some pointers there, if there is help.
>> use thee the hstore!
>>
>> postgres=# select * from each('a=>1,b=>2');
>> key | value
>> -+---
>> a | 1
>> b | 2
>> (2 rows)
>>
>> (if you do stick with arrays, use unnest() -- it can be built for
>> older versions if you don't have it).
>>
>> merlin
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Create a custom type with two text components and use that in an array.
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql died in midst of session
This has not happened before to me. I'm running postgres-9.0.4 on Slackware-13.1. I've been working on the command line using the psql shell updating and fixing a table when the application failed on me: PANIC: could not open file "pg_xlog/00010046" (log file 0, segment 70): Permission denied PANIC: could not open file "pg_xlog/00010046" (log file 0, segment 70): Permission denied The connection to the server was lost. Attempting reset: Failed. !> !> q -> -> !> Terminated When I try to restart postgres (as user postgres) I get this: postgres@salmo:~$ postgres -D /usr/local/pgsql/data & [1] 17783 postgres@salmo:~$ LOG: database system was interrupted while in recovery at 2011-10-12 13:00:05 PDT LOG: database system was not properly shut down; automatic recovery in progress LOG: consistent recovery state reached at 0/4581E51C LOG: redo starts at 0/457EC9C4 PANIC: could not open file "pg_xlog/00010046" (log file 0, segment 70): Permission denied LOG: startup process (PID 17784) was terminated by signal 6: Aborted LOG: aborting startup due to startup process failure [1]+ Exit 1 postgres -D /usr/local/pgsql/data pg_xlog is owned by postgres.users and has permissions 755. The /tmp/.S file is not present, either. Please advise me how to recover from whatever happened so I can get postgres up and running again. Thanks, Rich -- 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] psql died in midst of session [FIXED]
On Wed, 12 Oct 2011, Rich Shepard wrote: Please advise me how to recover from whatever happened so I can get postgres up and running again. Fixed. I noticed that the referenced pg_log/ file was owned by root.root rather than by postgres.users so I chown and that did the trick. Strange. Guess it was happy until time to write to the file. Apologies for posting, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostGIS: Approximating a house number from street address range
Hi, I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the street. I'm now trying to figure out the best way to programmatically approximate the nearest house number to the given lat/lng point. Here's an example of a row containing the street data: -[ RECORD 1 ]- [...] l_adddirfg | Same Direction l_hnumf| 3219 l_hnuml| 3235 l_stname_c | Breen Road North-west r_adddirfg | Same Direction r_hnumf| 3224 r_hnuml| 3236 r_stname_c | Breen Road North-west the_geom | 010520E61001000102000200B0F6990E78885CC088DF2B5F3C8C49400875B39A89885CC0A0BCA6AC4B8C4940 So, given a lat/lng coordinate that lies near the "the_geom" line segment, a person could tell visually which side of the street the point is on (left or right side), and how far along the segment it is -- thereby approximating a house number. For example, if the point lies on the right side, three-quarters down the street, I would use the fields r_hnumf (right side, first number) and r_hnuml (right side, last number)... The street address is probably close to: 3232 Breen Road North-west What I'm looking for is a best practice in either computing/approximating this in PostGIS (which I'm new to), or in the application layer once the row is fetched. Any ideas? Thanks! ...Rene -- 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] Monitoring Replication
On Wed, Oct 12, 2011, Brandon Phelps wrote: > I use Nagios to monitor various things on a few servers and have > recently set up a hot-standby server and would obviously like to > include the state of streaming replication in my monitoring. > > [...] > > The confusion I have is how exactly can I determine just how far > behind the replication is during loads? Currently with no traffic > (servers not in production yet) sent_location on the master is > "A/10018560" and pg_last_xlog_receive_location() on the standby also > returns "A/10018560"... How far apart can these be for me to start > worrying? I could make a bit more sense of all this if they were > simple timestamps or something, but the hex values returned boggle my > mind. > > Any advice on these issues or other tips on monitoring the replication > would be greatly appreciated. Brandon: I'm using this script for Mon, you should be able to adapt it to whatever language and monitoring system you please. http://www.martini.nu/misc/db_replication.monitor.txt -- Mahlon E. Smith http://www.martini.nu/contact.html pgpod4O8ZD9Fy.pgp Description: PGP signature
Re: [GENERAL] how to key/value iterate in stored function
I tried that, but it did not work.
what i am looking for is a means to iterate over an array or list fetching both
the key and the value.
if you have such an example, it would be appreciated.
Create a custom type with two text components and use that in an array.
David J.
On 10/12/2011 1:11 PM, David Johnston wrote:
On Oct 12, 2011, at 14:43, "J.V." wrote:
I tried hstore with no luck.
How do I initialize the array?
How do I loop through each key/value pair in a stored function to retrieve the
key and value for each item in the list?
I need a list of key value pairs. Any options there?
J.V.
On 10/12/2011 8:51 AM, Merlin Moncure wrote:
On Wed, Oct 12, 2011 at 8:46 AM, J.V. wrote:
I want to create a variable that is an array or list of key/value pairs.
The key would be a table name and the value would be a sql statement or a
value or list of values for which I could extract to create a sql statement
and execute.
I have tried experimenting with various arrays, but there is no clear
example or documentation. The key and the value would be text.
I want to iterate over the entire array (or list), so would need an example
on that or some pointers there, if there is help.
use thee the hstore!
postgres=# select * from each('a=>1,b=>2');
key | value
-+---
a | 1
b | 2
(2 rows)
(if you do stick with arrays, use unnest() -- it can be built for
older versions if you don't have it).
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Create a custom type with two text components and use that in an array.
David J.
--
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 8.3 replication causing corruption
>> >> Should replication cause corruption on the secondary when stopping/starting >> the primary? > > I wasn't aware 8.3 had any built in replication? what sort of replication > add-ons are you using? > Continuous archiving / WAL shipping as described in: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html -- 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] Using constraint exclusion with 2 floats
On Wed, Oct 12, 2011 at 10:16 AM, Julien Rouhaud wrote: > Hi everyone, > Is there an easy way (that I maybe missed) to use constraint exclusion with > 2 floats ? > Must I find an extension the temporal extension which create a new type with > 2 timestamp or can I usethe && operator with 2 fields ? There's nothing in constraint exclusion that depends upon specific datatypes. Let us know if you find a problem with floats. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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 8.3 replication causing corruption
On Tue, Oct 11, 2011 at 12:12 AM, Bob Hatfield wrote: > Should replication cause corruption on the secondary when stopping/starting > the primary? (pg 8.3.12, windows 2008 R2 on both servers) No, it shouldn't. Any duplicate keys would represent a serious error. It sounds like you're using warm standby, but when you say run pg_start_backup() AFTER each nightly backup I admit to being confused. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Are file system level differential/incremental backups possible?
Is it possible to do a full file system level backup of the data directory, say once a week, and differentials or incrementals daily? I'm wondering if there are files that would normally be removed that a restore: Full then diff/inc would not remove and perhaps corrupt/confuse things. Process: Saturday: Full backup (reset archive bits) of data dir with database shutdown Sunday: Differential (don't reset archive bits) of data dir with database shutdown Monday: Differential (don't reset archive bits) of data dir with database shutdown Wednesday: Restore to test server using Saturday's Full and Monday's Differential. Obviously this works for regular files/file systems; however, I'm not sure this is a good method with postgresql as the resulting data dir *may* (?) contain extra files (or other issues)? Note: our database is 850GB (Windows 2008 R2 pg version 8.3.12) -- 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 8.3 replication causing corruption
>> >> Should replication cause corruption on the secondary when stopping/starting >> the primary? > > I wasn't aware 8.3 had any built in replication? what sort of replication > add-ons are you using? > Continuous archiving / WAL shipping as described in: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html -- 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] Using constraint exclusion with 2 floats
Thank you for your answer. I'm sorry I really didn't explained well my problem :/ For example if I have a table test like this : CREATE TABLE test (min real not null, max real not null, desc character varying not null); and I want a constraint exclusion to make sure the range min/max doens't overlap I can't write ALTER TABLE test add constraint test_exclude EXCLUDE USING btree ((min,max) WITH &&) I saw the extension temporal gives a new type PERIOD and has operators like &&, but only for timestamp, so I'm wondering if I must code something something similar or if there's is an easier way On Wed, Oct 12, 2011 at 11:23 PM, Simon Riggs wrote: > On Wed, Oct 12, 2011 at 10:16 AM, Julien Rouhaud > wrote: > > Hi everyone, > > Is there an easy way (that I maybe missed) to use constraint exclusion > with > > 2 floats ? > > Must I find an extension the temporal extension which create a new type > with > > 2 timestamp or can I usethe && operator with 2 fields ? > > There's nothing in constraint exclusion that depends upon specific > datatypes. > > Let us know if you find a problem with floats. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [GENERAL] Are file system level differential/incremental backups possible?
As there's one file for each object, a single update on each would make you to copy the all the file again. I heard there was tool to make differentiel copy of a part of a file but I don't know if it's really efficient. Anyway, a better way for you would be to do a regular backup (with pg_start_backup, copy and pg_stop_backup) and then use wal archive_command to keep the xlogs between 2 full backups. On Wed, Oct 12, 2011 at 11:30 PM, Bob Hatfield wrote: > Is it possible to do a full file system level backup of the data > directory, say once a week, and differentials or incrementals daily? > > I'm wondering if there are files that would normally be removed that a > restore: Full then diff/inc would not remove and perhaps > corrupt/confuse things. > > Process: > Saturday: Full backup (reset archive bits) of data dir with database > shutdown > Sunday: Differential (don't reset archive bits) of data dir with > database shutdown > Monday: Differential (don't reset archive bits) of data dir with > database shutdown > Wednesday: Restore to test server using Saturday's Full and Monday's > Differential. > > Obviously this works for regular files/file systems; however, I'm not > sure this is a good method with postgresql as the resulting data dir > *may* (?) contain extra files (or other issues)? > > Note: our database is 850GB (Windows 2008 R2 pg version 8.3.12) > > -- > 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 8.3 replication causing corruption
>> Should replication cause corruption on the secondary when stopping/starting >> the primary? (pg 8.3.12, windows 2008 R2 on both servers) > > No, it shouldn't. Any duplicate keys would represent a serious error. > > It sounds like you're using warm standby, but when you say run > pg_start_backup() AFTER each nightly backup I admit to being confused. > Thanks for your response. Perhaps a quick process flow would help clarify: Scenario 1 (no errors): a) create warm standby and let run throughout the day (works great) b) at the end of the day, trigger the secondary and run a reindex for testing (no errors) Scenario 2 (errors): a) create warm standby and let run throughout the day (works great) b) nightly backup: shutdown pg on primary, do a file system copy (for backup later), start pg again on primary c) the next morning, trigger the secondary and run a re-index for testing (ERRORS as described in thread) Side note: the data copied in 2.b is fine and also passes a full re-index. Scenario 3 (work around - not a very good one): a) create warm standby and let run throughout the day (works great) b) nightly backup: shutdown pg on primary, do a file system copy (for backup later), start pg again on primary c) the next morning, re-create the warm standby (this is where I may have confused you with doing a pg_start_backup after nightly backups) 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] Drill-downs and OLAP type data
Hi, > The *problem* with Greenplum is that it's ultra-expensive once you leave the > CE version - and you're not supposed to be using the CE version for > commercial usage last I read the license. Has that changed? Not sure. I haven't seen something like that in the license. After POC we bought HW and license from EMC > The last pricing I saw was around $16k per CPU (it may have been per core?). > At that point, you're now in the realm of SQL Server Analysis and DB2 SPSS. Yeah, it is not cheap but it is "drop in" replacement for Postgres and we can connect to it from PHP without any issues. (PHP PDO + ODBC doesn't work very well) > > Our problem is pairing > up a web based GUI to a database we love using. Doesn't seem possible, > because the user-friendly OLAP / data analysis / dashboard tools are all > expecting functionality that PG doesn't have. Could you please name few of them? I'm looking for something like Tableau but web based (without .Net) and hackable (our DB is partially encrypted) > It sounds like, we're either choosing a different DB to work with the pretty > GUI tools, or writing a GUI tool to work with PG. I think you are right here. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- 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 8.3 replication causing corruption
On Wed, Oct 12, 2011 at 10:51 PM, Bob Hatfield wrote: >>> Should replication cause corruption on the secondary when stopping/starting >>> the primary? (pg 8.3.12, windows 2008 R2 on both servers) >> >> No, it shouldn't. Any duplicate keys would represent a serious error. >> >> It sounds like you're using warm standby, but when you say run >> pg_start_backup() AFTER each nightly backup I admit to being confused. >> > > Thanks for your response. Perhaps a quick process flow would help clarify: > > Scenario 1 (no errors): > a) create warm standby and let run throughout the day (works great) > b) at the end of the day, trigger the secondary and run a reindex for > testing (no errors) > > Scenario 2 (errors): > a) create warm standby and let run throughout the day (works great) > b) nightly backup: shutdown pg on primary, do a file system copy (for > backup later), start pg again on primary > c) the next morning, trigger the secondary and run a re-index for > testing (ERRORS as described in thread) I see no reason to expect errors there. Something about your setup is suspect. Disks perhaps. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Are file system level differential/incremental backups possible?
> Anyway, a better way for you would be to do a regular backup (with > pg_start_backup, copy and pg_stop_backup) and then use wal archive_command to > keep the xlogs between 2 full backups. Thanks Julien. Can pg_start/stop_backup() be used for regular full file system backups? All of the documentation I've read only refers to using those for warm standby/wal shipping methods. -- 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 8.3 replication causing corruption
> Something about your setup is suspect. Disks perhaps. Disk: Fusion IOdrive (1.2TB NAND drive) I've read that one should set wal_sync_method=fsync_writethrough for Windows servers. It's currently set to open_datasync, I have no idea what effect that will have other than I've read less performance. Thoughts? -- 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] Are file system level differential/incremental backups possible?
On Thu, Oct 13, 2011 at 12:04 AM, Bob Hatfield wrote: > > Anyway, a better way for you would be to do a regular backup (with > pg_start_backup, copy and pg_stop_backup) and then use wal archive_command > to keep the xlogs between 2 full backups. > > Thanks Julien. Can pg_start/stop_backup() be used for regular full > file system backups? All of the documentation I've read only refers > to using those for warm standby/wal shipping methods. > Yes, and it's the only way to do a file backup without stopping the server. Careful, the command can last a while as it forces a checkpoint (see the doc for more details). It's used for warm standby to create a copy of the server, before the wals that'll be generated can continue to restore it.
Re: [GENERAL] Are file system level differential/incremental backups possible?
On October 12, 2011 03:04:30 PM Bob Hatfield wrote: > > Anyway, a better way for you would be to do a regular backup (with > > pg_start_backup, copy and pg_stop_backup) and then use wal > > archive_command to keep the xlogs between 2 full backups. > > Thanks Julien. Can pg_start/stop_backup() be used for regular full > file system backups? All of the documentation I've read only refers > to using those for warm standby/wal shipping methods. The base backup necessary to initialize a warm standby server is a full file system backup of the database, which can also be used for restores to any point in time after the base backup is completed, assuming you also have all the archived WAL files. -- 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] Are file system level differential/incremental backups possible?
> The base backup necessary to initialize a warm standby server is a full file > system backup of the database, which can also be used for restores to any > point in time after the base backup is completed, assuming you also have all > the archived WAL files. Thanks to both of you. I currently use the base backup technique for use with a warm standby server but was not aware I could use that for full file system level backups as well. In fact, I currently run a warm standby server (created from a base backup every several weeks) as well as stop/start the pg process to do a full file system level copy each night. If I think about this a bit, I'm sure there's a more efficient way of doing this. (As well as may solve another problem I'm having (another post) with the standby server's database getting corrupt after stopping/starting the primary's pg process.) -- 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 key/value iterate in stored function
On Wed, Oct 12, 2011 at 1:43 PM, J.V. wrote: > I tried hstore with no luck. > > How do I initialize the array? > > How do I loop through each key/value pair in a stored function to retrieve > the key and value for each item in the list? > > I need a list of key value pairs. Any options there? it's all in the docs: postgres=# select * from each(hstore(ARRAY['a','b'], ARRAY['1','2'])); key | value -+--- a | 1 b | 2 (2 rows) dont see what's so hard 'bout that. 9.0+ hstore is superior to the composite type method in every way I can think of unless you are storing explictly non text value in the type and you are not transferring the compacted list to the client. hstore is much more flexible in terms of getting data in/out, searching, etc. as a bonus you have gist indexing if you need it, etc etc. merlin -- 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: Approximating a house number from street address range
On 10/12/2011 01:01 PM, René Fournier wrote: Hi, I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the street. I'm now trying to figure out the best way to programmatically approximate the nearest house number to the given lat/lng point. Here's an example of a row containing the street data: -[ RECORD 1 ]- [...] l_adddirfg | Same Direction l_hnumf| 3219 l_hnuml| 3235 l_stname_c | Breen Road North-west r_adddirfg | Same Direction r_hnumf| 3224 r_hnuml| 3236 r_stname_c | Breen Road North-west the_geom | 010520E61001000102000200B0F6990E78885CC088DF2B5F3C8C49400875B39A89885CC0A0BCA6AC4B8C4940 So, given a lat/lng coordinate that lies near the "the_geom" line segment, a person could tell visually which side of the street the point is on (left or right side), and how far along the segment it is -- thereby approximating a house number. For example, if the point lies on the right side, three-quarters down the street, I would use the fields r_hnumf (right side, first number) and r_hnuml (right side, last number)... The street address is probably close to: 3232 Breen Road North-west What I'm looking for is a best practice in either computing/approximating this in PostGIS (which I'm new to), or in the application layer once the row is fetched. Any ideas? Thanks! ...Rene Is this the only format you have the data in? If you had two rectangles (one for each side of the street), and each rect had an address, this would be a lot simpler. Is that geom a line? rectangle? Do you have a layer that has lots or parcels? -Andy -- 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: Approximating a house number from street address range
On 10/12/2011 06:29 PM, Andy Colson wrote: On 10/12/2011 01:01 PM, René Fournier wrote: Hi, I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the street. I'm now trying to figure out the best way to programmatically approximate the nearest house number to the given lat/lng point. Here's an example of a row containing the street data: -[ RECORD 1 ]- [...] l_adddirfg | Same Direction l_hnumf | 3219 l_hnuml | 3235 l_stname_c | Breen Road North-west r_adddirfg | Same Direction r_hnumf | 3224 r_hnuml | 3236 r_stname_c | Breen Road North-west the_geom | 010520E61001000102000200B0F6990E78885CC088DF2B5F3C8C49400875B39A89885CC0A0BCA6AC4B8C4940 So, given a lat/lng coordinate that lies near the "the_geom" line segment, a person could tell visually which side of the street the point is on (left or right side), and how far along the segment it is -- thereby approximating a house number. For example, if the point lies on the right side, three-quarters down the street, I would use the fields r_hnumf (right side, first number) and r_hnuml (right side, last number)... The street address is probably close to: 3232 Breen Road North-west What I'm looking for is a best practice in either computing/approximating this in PostGIS (which I'm new to), or in the application layer once the row is fetched. Any ideas? Thanks! ...Rene Is this the only format you have the data in? If you had two rectangles (one for each side of the street), and each rect had an address, this would be a lot simpler. Is that geom a line? rectangle? Do you have a layer that has lots or parcels? -Andy Oh, also, there is a PostGIS list that might be helpful. -Andy -- 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: Approximating a house number from street address range
On 10/12/2011 06:29 PM, Andy Colson wrote: On 10/12/2011 01:01 PM, René Fournier wrote: Hi, I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the street. I'm now trying to figure out the best way to programmatically approximate the nearest house number to the given lat/lng point. Here's an example of a row containing the street data: -[ RECORD 1 ]- [...] l_adddirfg | Same Direction l_hnumf | 3219 l_hnuml | 3235 l_stname_c | Breen Road North-west r_adddirfg | Same Direction r_hnumf | 3224 r_hnuml | 3236 r_stname_c | Breen Road North-west the_geom | 010520E61001000102000200B0F6990E78885CC088DF2B5F3C8C49400875B39A89885CC0A0BCA6AC4B8C4940 So, given a lat/lng coordinate that lies near the "the_geom" line segment, a person could tell visually which side of the street the point is on (left or right side), and how far along the segment it is -- thereby approximating a house number. For example, if the point lies on the right side, three-quarters down the street, I would use the fields r_hnumf (right side, first number) and r_hnuml (right side, last number)... The street address is probably close to: 3232 Breen Road North-west What I'm looking for is a best practice in either computing/approximating this in PostGIS (which I'm new to), or in the application layer once the row is fetched. Any ideas? Thanks! ...Rene Is this the only format you have the data in? If you had two rectangles (one for each side of the street), and each rect had an address, this would be a lot simpler. Is that geom a line? rectangle? Do you have a layer that has lots or parcels? -Andy Ah, its a line: MULTILINESTRING((-114.1323277 51.0955924,-114.1333987 51.096059401)) But then you have a problem. If this is a street line, and its going north/south, great, but what if its going east/west? What's the right hand side of a horizontal line? -Andy -- 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: Approximating a house number from street address range
On 10/12/2011 06:38 PM, Andy Colson wrote: On 10/12/2011 06:29 PM, Andy Colson wrote: On 10/12/2011 01:01 PM, René Fournier wrote: Hi, I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the street. I'm now trying to figure out the best way to programmatically approximate the nearest house number to the given lat/lng point. Here's an example of a row containing the street data: -[ RECORD 1 ]- [...] l_adddirfg | Same Direction l_hnumf | 3219 l_hnuml | 3235 l_stname_c | Breen Road North-west r_adddirfg | Same Direction r_hnumf | 3224 r_hnuml | 3236 r_stname_c | Breen Road North-west the_geom | 010520E61001000102000200B0F6990E78885CC088DF2B5F3C8C49400875B39A89885CC0A0BCA6AC4B8C4940 So, given a lat/lng coordinate that lies near the "the_geom" line segment, a person could tell visually which side of the street the point is on (left or right side), and how far along the segment it is -- thereby approximating a house number. For example, if the point lies on the right side, three-quarters down the street, I would use the fields r_hnumf (right side, first number) and r_hnuml (right side, last number)... The street address is probably close to: 3232 Breen Road North-west What I'm looking for is a best practice in either computing/approximating this in PostGIS (which I'm new to), or in the application layer once the row is fetched. Any ideas? Thanks! ...Rene Is this the only format you have the data in? If you had two rectangles (one for each side of the street), and each rect had an address, this would be a lot simpler. Is that geom a line? rectangle? Do you have a layer that has lots or parcels? -Andy Ah, its a line: MULTILINESTRING((-114.1323277 51.0955924,-114.1333987 51.096059401)) But then you have a problem. If this is a street line, and its going north/south, great, but what if its going east/west? What's the right hand side of a horizontal line? -Andy Wow. Neet. I Learned something new. PostGIS never ceases to amaze me. Find the point on a line closest to a click point: http://postgis.refractions.net/docs/ST_Line_Locate_Point.html Then use http://postgis.refractions.net/documentation/manual-svn/ST_Azimuth.html to find the angle between two points. The angle can tell you if the click point is left/right (or above/below) the street. I googled two things that might offer you more help: "postgis line direction" and "postgis point closest to line". Ok, I'll quit spamming the list now. (Oh yeah, I have some med's I can sell ya!) -Andy -- 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] I need to load mysql dump to postgres...
On 10/12/2011 03:45 AM, Devrim GÜNDÜZ wrote: On Tue, 2011-10-11 at 22:37 -0400, unclebob wrote: Is there a program which would just parse mysql dump file and load data to postgresql using plain sql inserts? EDB has an open source and free tool for this: http://www.enterprisedb.com/migrationwizard-11 If you are using RPM based distros, there are also RPMs of this in http://yum.postgresql.org -- package name ise mysqlmigrator. Regards, Devrim, I'm under debian squeeze and pgsql 8.4. Do you know which version of mysqlmigrator I can try? 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] how to find primary key field name?
On 12/10/11 11:54, J.V. wrote: If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = and constraint_type = 'PRIMARY KEY'; will return the constraint name, but given the table_name and the constraint_name, how do I find the database column/field name associated with that primary key? J.V. I think this version is probably more directly useful, and a bit simpler: SELECT ci.relname AS "Index", a.attname AS "Primary Key Col" FROM pg_index i JOIN pg_class cr ON (cr.oid = i.indrelid) JOIN pg_attribute a ON (a.attrelid = cr.oid) JOIN pg_class ci ON (ci.oid = i.indexrelid) WHERE i.indisprimary AND cr.relname = 'salary' AND EXISTS (SELECT 1 FROM unnest(i.indkey) p(c) WHERE p.c = a.attnum) ORDER BY a.attname Cheers, Gavin /**/;/**/ -- 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 find primary key field name?
On 12/10/11 11:54, J.V. wrote: If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = and constraint_type = 'PRIMARY KEY'; will return the constraint name, but given the table_name and the constraint_name, how do I find the database column/field name associated with that primary key? J.V. You might find the following useful: SELECT cr.relname AS "Table", ci.relname AS "Index", a.attname AS "Primary Key Col" FROM pg_index i JOIN pg_class cr ON (cr.oid = i.indrelid) JOIN pg_namespace n ON (n.oid = cr.relnamespace) JOIN pg_attribute a ON (a.attrelid = cr.oid) JOIN pg_class ci ON (ci.oid = i.indexrelid) WHERE i.indisprimary AND n.nspname = 'public' AND EXISTS (SELECT 1 FROM unnest(i.indkey) p(c) WHERE p.c = a.attnum) ORDER BY cr.relname, a.attname /**/;/**/ Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing "uuid-ossp" library in Postgres 9.1. (new Extension feature)
The "uuid-ossp" library enables you to generate UUID values server-side in Postgres. http://www.postgresql.org/docs/current/static/uuid-ossp.html The technique to install this library changed as of Postgres 9.1, because of the new Extension feature. Installing and uninstalling are now easier. I have an overview on my blog, but I'm posting the brief steps here for posterity. To see what extensions are already installed in your Postgres, run this SQL: select * from pg_extension; To see if the "uuid-ossp" extension is available, run this SQL: select * from pg_available_extensions; To install/load the extension, run this SQL: CREATE EXTENSION "uuid-ossp"; I found the quote marks to be required despite the doc being contrary. Doc on loading extensions: http://www.postgresql.org/docs/current/static/sql-createextension.html My blog post on this topic: http://crafted-software.blogspot.com/2011/10/extensions-in-postgres.html To install this library in versions of Postgres before 9.1: http://crafted-software.blogspot.com/2011/10/using-uuid-library-in-postgres.html --Basil Bourque -- 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] Installing "uuid-ossp" library in Postgres 9.1. (new Extension feature)
On 10/12/11 7:10 PM, Basil Bourque wrote: To install/load the extension, run this SQL: CREATE EXTENSION "uuid-ossp"; I found the quote marks to be required despite the doc being contrary. probably because of the minus sign... otherwise, thats parsed as uuid minus ossp :-/ -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Help on PostgreSQL
Hello, > 2.Is there any enterprise version available with all features? We just completed migrating one of our products to PostgreSQL and load testing it. My suggestion- if your product uses stored procedures/packages heavily, have a look at EnterpriseDB. Otherwise, try plain simple PostgreSQL. That is what we did. We used ora2pg for database migration and orafce (http://pgfoundry.org/projects/orafce/) to minimize code changes. Since we did not have many procedures/packages it worked very well. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [GENERAL] Conceptual Architecture
Hello, > > Could you please point us to a simple white paper/doc which describes > > the Conceptual Architecture of PostgresSQL? I found these very useful. http://www.postgresql.org/files/developer/tour.pdf http://www.postgresql.org/files/developer/internalpics.pdf http://www.westnet.com/~gsmith/content/postgresql/InsideBufferCache.pdf Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
[GENERAL] Dynamic sql real examples
Hi all, I have lately learned what is dynamic sql and one of the most interesting features of it to me is that we can use dynamic columns names and tables. But I cannot think about useful real life examples. The only one that came into my mind is statistical table. Let`s say that we have table with name, type and created_data. Then we want to have a table that in columns are years from created_data column and in row type and number of names created in years. (sorry for my English) What can be other useful real life examples of using dynamic sql with column and table as parameters? How do you use it? I am particulary interested in examples that do not contain administrative things or database convertion or something like that, I am looking for examples where the code in example java is more complicated than using a dynamic sql in for example stored procedure. Thanks for any suggestions and help :) regards Gabe
Re: [GENERAL] Dynamic sql real examples
Thx Gavin, any other suggestions from others? Gabe 2011/10/13 Gavin Flower > On 13/10/11 17:55, Gabriel Filipiak wrote: > > Hi all, > > I have lately learned what is dynamic sql and one of the most interesting > features of it to me is that we can use dynamic columns names and tables. > But I cannot think about useful real life examples. The only one that came > into my mind is statistical table. > > Let`s say that we have table with name, type and created_data. Then we want > to have a table that in columns are years from created_data column and in > row type and number of names created in years. (sorry for my English) > > What can be other useful real life examples of using dynamic sql with > column and table as parameters? How do you use it? > > > I am particulary interested in examples that do not contain administrative > things or database convertion or something like that, I am looking for > examples where the code in example java is more complicated than using a > dynamic sql in for example stored procedure. > > Thanks for any suggestions and help :) regards Gabe > > Hi Gabe, > > I have experience both in the design and implementation of Java systems, as > well as in creating and querying databases (from Java and directly using > SQL). > > A complex query will be executed by PostgreSQL far more efficiently than a > series of simpler queries – even if both are initiated via JDBC. > > An example where dynamic SQL would useful would be in SQL generated to > support a search function with multiple fields, some of which are optional. > From memory, when I did this in Java, the Java application constructed the > query and passed it via JDBC to the database. > > In another situation, I constructed stored procedures in Sybase TransactSQL > with dynamically executed SQL to support a report generation program where > some of the search fields where optional. PostgreSQL is easier to work with, > but it was an existing database. Also using Java was not practicable. > > Be very careful to avoid SQL injection attacks. Consider using the > functions: > > quote_ident(string text) > > and > > quote_literal(string text) > > (see the section '9.4. String Functions and Operators' of the PostgreSQL > 9.1.1 manual) > > > Cheers, > Gavin > > > > > > . >
Re: [GENERAL] Dynamic sql real examples
2011/10/13 Gavin Flower > On 13/10/11 18:35, Gabriel Filipiak wrote: > > Thx Gavin, > > any other suggestions from others? > > Gabe > > 2011/10/13 Gavin Flower > >> On 13/10/11 17:55, Gabriel Filipiak wrote: >> >> Hi all, >> >> I have lately learned what is dynamic sql and one of the most >> interesting features of it to me is that we can use dynamic columns names >> and tables. But I cannot think about useful real life examples. The only one >> that came into my mind is statistical table. >> >> Let`s say that we have table with name, type and created_data. Then we >> want to have a table that in columns are years from created_data column and >> in row type and number of names created in years. (sorry for my English) >> >> What can be other useful real life examples of using dynamic sql with >> column and table as parameters? How do you use it? >> >> >> I am particulary interested in examples that do not contain >> administrative things or database convertion or something like that, I am >> looking for examples where the code in example java is more complicated than >> using a dynamic sql in for example stored procedure. >> >> Thanks for any suggestions and help :) regards Gabe >> >> Hi Gabe, >> >> I have experience both in the design and implementation of Java systems, >> as well as in creating and querying databases (from Java and directly using >> SQL). >> >> A complex query will be executed by PostgreSQL far more efficiently than a >> series of simpler queries – even if both are initiated via JDBC. >> >> An example where dynamic SQL would useful would be in SQL generated to >> support a search function with multiple fields, some of which are optional. >> From memory, when I did this in Java, the Java application constructed the >> query and passed it via JDBC to the database. >> >> In another situation, I constructed stored procedures in Sybase >> TransactSQL with dynamically executed SQL to support a report generation >> program where some of the search fields where optional. PostgreSQL is easier >> to work with, but it was an existing database. Also using Java was not >> practicable. >> >> Be very careful to avoid SQL injection attacks. Consider using the >> functions: >> >> quote_ident(string text) >> >> and >> >> quote_literal(string text) >> >> (see the section '9.4. String Functions and Operators' of the PostgreSQL >> 9.1.1 manual) >> >> >> Cheers, >> Gavin >> >> >>Hi Gabe, > > Please do not 'top post'. In these mailings lists, you are expected to add > your comments either interpersed, or (more normally) at the bottom. This > allows people to read the context, before they read your comments. > > > Cheers, > Gavin > Sorry about that. Gabe
Re: [GENERAL] Dynamic sql real examples
Please do not 'top post'. In these mailings lists, you are expected to add your comments either interpersed, or (more normally) at the bottom. This allows people to read the context, before they read your comments. Sorry about that. the other half of not top posting is editting the quotes so you leave out all the extraneous stuff but what you're actually replying to. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Dynamic sql real examples
Hello It's really depends on client software and architecture. Dynamic SQL is interesting for some use cases when you use a stored procedures, when you dynamically create tables based on metadata and when you access these tables. We had a object oriented database with interface in stored procedures. Some classes was stored with own tables, some classes was stored in common tables. But a format was transparent for client. Some-times you have to use a dynamic sql as workaround - there is no other way - like this http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql Regards Pavel Stehule 2011/10/13 Gabriel Filipiak : > Hi all, > > I have lately learned what is dynamic sql and one of the most interesting > features of it to me is that we can use dynamic columns names and tables. > But I cannot think about useful real life examples. The only one that came > into my mind is statistical table. > > Let`s say that we have table with name, type and created_data. Then we want > to have a table that in columns are years from created_data column and in > row type and number of names created in years. (sorry for my English) > > What can be other useful real life examples of using dynamic sql with column > and table as parameters? How do you use it? > > I am particulary interested in examples that do not contain administrative > things or database convertion or something like that, I am looking for > examples where the code in example java is more complicated than using a > dynamic sql in for example stored procedure. > > Thanks for any suggestions and help :) regards Gabe > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
