[GENERAL] PostgreSQL with ZFS on Linux
Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien
Re: [GENERAL] Need Help to implement Proximity search feature
Thanks for your reply, i am totally new to Postgis. we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. Regards, Itishree On Wed, Jan 15, 2014 at 11:28 PM, Oleg Bartunov obartu...@gmail.com wrote: check knn search, http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla itishree.su...@gmail.com wrote: Deal all, In my project, there is a requirement to implement proximity search feature. We are running a mobile app, for which proximity search is require. Can any one guide me how i can achieve this using postgis, or is there any other way i can achieve this. We are using postgresql 9.2. Thanks in advance . Regards, Itishree
Re: [GENERAL] PostgreSQL with ZFS on Linux
On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion s...@thestrangefactory.comwrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien FYI, a recent (Sept. 2013) presentation I found about using ZoL in production (albeit, not with PostgreSQL) and the current status of the project: http://lanyrd.com/2013/linuxcon-north-america/scqmfb/
Re: [GENERAL] Index space growing even after cleanup via autovacuum in Postgres 9.2
On Wed, Jan 15, 2014 at 6:07 PM, Tirthankar Barari tbar...@verizon.com wrote: My tables are: table test_metric ( id varchar(255) not null, // just auto generated uuid from app timestamp timestamp not null, version int4, entity_id varchar(255) not null, primary key (id, timestamp) ); Indexes: test_metric_pkey1 PRIMARY KEY, btree (id) test_metric_entity_id_timestamp_key UNIQUE CONSTRAINT, btree (entity_id, timestamp) AND table test_metric_metrics ( metric_id varchar(255) not null, metrics float8, metrics_key varchar(255) not null, primary key (metric_id, metrics_key) ); Indexes: test_metric_metrics_pkey PRIMARY KEY, btree (metric_id, metrics_key) Foreign-key constraints: fk3b8e13abb63406d5 FOREIGN KEY (metric_id) REFERENCES test_metric(id) Basically, test_metric holds the timestamp and some metadata and test_metric_metrics holds the set of key/value pairs for the give entity and timestamp in the parent table. Is it possible to partition the second table by timestamp field from first table? ... I think not. Although you could copy the timestamp field into the second table, but then things will begin to get hairy, as you'll need to maintain redirection rules for insertions on both tables, and I do not know from memory which are the rules when mixing foreign keys and inheritance, and will need to test them anyway to convince myself it works. To dessign a solution for these, knowledge of the application and the access patterns is needed. I just wanted to point that for maintaining rolling logs of data inheritance plus some logic can greatly enhance your performance, as you do not need deletes, just table droppings which are normally faster. I do it to maintain call records, but I have logic for my particular access pattern, which creates partitions on demand and just uses copy against the relevant partition exploiting timestamp locality and invariance ( cdrs are normally inserted for recent calls, and timestamps do not normally change ) and it has made maintenance much easier ( as I just coalesce old paritions into unheriting archive tables and drop them ). You maybe able to dessign something like this, but if you begin to have complex relatinoships, probably the way to go until you explore the relationships is to just cranck up the deleting / vacuuming frequency ( many small delete / vacuum normally lead to more total time but less impact on db performance, as it has been pointed previously ), and, if the tables use the classical pattern for log tables ( insert at one end, delete at the other, extremely rare updates ) it will probably perform better if excluded from autovacuum and vacuumed explictly after deletion batches, or just periodically ( as inserting does not leave many oportunities to recover space, but you may want to analyze if vaccum is set infrequently ). 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
Re: [GENERAL] PostgreSQL with ZFS on Linux
http://www.unix-experience.fr/2013/2451/ FreeBSD is also a very mature platform for ZFS/postgresql. On 16/01/2014 11:57, Sébastien Lorion wrote: On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion s...@thestrangefactory.com mailto:s...@thestrangefactory.com wrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien FYI, a recent (Sept. 2013) presentation I found about using ZoL in production (albeit, not with PostgreSQL) and the current status of the project: http://lanyrd.com/2013/linuxcon-north-america/scqmfb/ -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] PostgreSQL with ZFS on Linux
On 16 January 2014 12:09, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: http://www.unix-experience.fr/2013/2451/ FreeBSD is also a very mature platform for ZFS/postgresql. More mature than on Linux even, as far as I know. If I had to choose an OS to use ZFS with, I'd go with either FreeBSD or Solaris. That said, I am biased to FreeBSD anyway; the only Linux installation that I own is the one in my Android phone, while I own several FreeBSD systems. On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion s...@thestrangefactory.com wrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien I do not consider ZFS an ideal file-system for databases. I'm not an expert on ZFS, but there are two features in ZFS that I think particularly make it less suitable for database use. One reason is that ZFS, as I understand it, is a log-structured file-system. That means that changes to files always go to the end of the file-system. If that file is a large frequently updated database table, records are going to be far apart and in fairly random order. That could seriously hurt performance. Secondly, with ZFS you need to reserve a significant amount of memory for the ZIL. That is memory that is not available to your database. Don't take my word for it, but I think the above points are worth investigating as is finding some file-system bench- marks where ZFS gets compared to, for example, UFS2 (FreeBSD), Ext4fs (Linux). Of course, the other side of the coin is ZFS's excellent flexibility. Cheers, Alban Hertroys -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] reading array[text] in C extension function
I'll auto-answer ;-)
Based on the function btoptions from postgres source, which takes aas a
first arg a text[] :
ArrayType *array;
Datum *dimdatums;
int ndim;
array = DatumGetArrayTypeP(dimensions);
Assert(ARR_ELEMTYPE(array) == TEXTOID);
pcinfo(after assert \n);
deconstruct_array(array, TEXTOID, -1, false, 'i',
dimdatums, NULL, ndim);
//construct the array to hold the result :
char ** final_dimension_array = (char **) pcalloc(ndim * sizeof(char * ) );
for (i = 0; i ndim; i++)
{
text *dimensiontext = DatumGetTextP(dimdatums[i]);
char *text_str = VARDATA(dimensiontext);
int text_len = VARSIZE(dimensiontext) - VARHDRSZ;
char *s;
char *p;
s = TextDatumGetCString(dimdatums[i]);
final_dimension_array[i] = s;
}
//pcinfo(end of the text retrieval\n);
Cheers,
Rémi-C
2014/1/15 Rémi Cura remi.c...@gmail.com
Hey,
I'm trying to use an array of text as input in a C function in a custom
extension.
the prototype of the sql function is :
CREATE OR REPLACE FUNCTION pc_subset( dimensions TEXT[])
it is called like :
pc_subset( ARRAY['X'::text,'Y'::text])
and the C function trying to read the text array (converting it to cstring
) :
text ** vals;
char ** cstring_array;
nelems = ARR_DIMS(arrptr)[0];
vals = (text**) ARR_DATA_PTR(arrptr);
cstring_array = (char **) pcalloc(nelems * sizeof(char * ) );
for (i3=0;i3nelems;i3++)
{
cstring_array[i3] = text_to_cstring( vals[i3]);
elog(INFO, elem %d of dim_array : %s\n,i3,cstring_array[i3]);
}
I crashes postgres because of a segfault.
Any help appreciated =)
Cheers,
Rémi-C
[GENERAL] Breakpoints are not triggered in analyze.c (debugging Postgresql in Visual studio)
Hi all, I've set up a developing environment on my windows using Visual Studio 2012, everything works fine, except that the breakpoints set in analyze.c are not triggered in debug mode (breakpoints in main.c and some initialization code worked well), and I'm sure that line has been executed since my own messages have been printed on the console. Does anyone have any experience debugging postgresql in Visual Studio? Thanks!
Re: [GENERAL] Breakpoints are not triggered in analyze.c (debugging Postgresql in Visual studio)
=?GB2312?B?RmVsaXgu0Ow=?= ygnhz...@gmail.com writes: I've set up a developing environment on my windows using Visual Studio 2012, everything works fine, except that the breakpoints set in analyze.c are not triggered in debug mode (breakpoints in main.c and some initialization code worked well), and I'm sure that line has been executed since my own messages have been printed on the console. Does anyone have any experience debugging postgresql in Visual Studio? There are two different source files named analyze.c; I wonder which one the debugger thinks you are talking about ... In gdb, the solution for this is to always start by setting a breakpoint by function name. Once you're stopped in a particular source file, gdb will assume that that file is meant by b linenumber references. Perhaps the same kind of trick will work with VS. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Any freeware graphic display of DDL software available?
Is there any free or cheap software that will read in DDL and output a graphic display of it? Preferably showing links for foreign keys. I know about Erwin, but it is too expensive. Thanks, Susan
Re: [GENERAL] Any freeware graphic display of DDL software available?
On Thu, Jan 16, 2014 at 08:45:44AM -0800, Susan Cassidy wrote: Is there any free or cheap software that will read in DDL and output a graphic display of it? Preferably showing links for foreign keys. pg_autodoc Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Any freeware graphic display of DDL software available?
Sparx architect Regards On Thursday, January 16, 2014 5:52 PM, Karsten Hilbert karsten.hilb...@gmx.net wrote: On Thu, Jan 16, 2014 at 08:45:44AM -0800, Susan Cassidy wrote: Is there any free or cheap software that will read in DDL and output a graphic display of it? Preferably showing links for foreign keys. pg_autodoc Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL with ZFS on Linux
On Thu, Jan 16, 2014 at 3:22 AM, Sébastien Lorion s...@thestrangefactory.comwrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien I would highly recommend you proceed with caution and lots of testing. If you look back in the archives of a week or so ago on this list, I posted with memory issues. After several helpful suggestions, we found the root cause to be postgresql coupled with ZFS on Linux. Basically, as I understand it (I may be a bit off), the ZFS Linux port has a middle layer to translate and emulate the Solaris kernel. Because of this, it isn't as efficient at memory mangement as a more native port like FreeBSD or Solaris itself. It is also prone to memory bugs/leaks with large rsync or file copy operations. Couple this with a memory hungry database and you can start having issues. In our case, we went from a server with 4 GB of RAM on a hardware RAID 10 and Postgresql 8.3 to a system with 32 GB of RAM, ZFS and Postgresql 9.3. Once we started pushing people to the new server and it got under load, postgres started being unable to allocate new RAM when it needed it. This was with same number of clients and same databases as the old 4 GB system, so it seemed crazy. With some great help of some of the people on this list, we started looking into what was going on and basically found that ZFS had taken 24 GB of RAM, about 18 of which was not cached/shared RAM but real RAM it was holding onto and wouldn't let go of. We have since moved to FreeBSD with ZFS and Postgres 9.3 (everything else the same) and performance has been awesome (as we were expecting out of the new server). If you really want ZFS, I would highly recommend looking into FreeBSD (Postgresql works great on it) or if you want to stick with Linux, look into mdadm with LVM or some other filesystem solution. A few helpful things we found: Some notes on ZFS on Linux of things to watch out for: http://utcc.utoronto.ca/~cks/space/blog/linux/ZFSonLinuxWeakAreas A great guide to figuring out how much RAM postgresql is actually using (assuming you run into RAM issues): http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ Preston
[GENERAL] commit fest 2014-01 wants reviewers
Commit fest 2014-01, the fourth and final commit fest in the PostgreSQL 9.4 development cycle, has started. What is a commit fest? https://wiki.postgresql.org/wiki/CommitFest As before, we need more people to help review submitted patches. How do you help reviewing? https://wiki.postgresql.org/wiki/Reviewing_a_Patch To sign up, go to https://commitfest.postgresql.org/action/commitfest_view?id=21 and put your name down for any patch you like. If you'd like to help but don't know which patch to take or have other questions, send me an email and I'll try to set you up. -- 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] Need Help to implement Proximity search feature
Please see the comment at the bottom of this post. On 16/01/14 22:52, itishree sukla wrote: Thanks for your reply, i am totally new to Postgis. At least you've got into it, I keep meaning do do so myself. we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. Regards, Itishree On Wed, Jan 15, 2014 at 11:28 PM, Oleg Bartunov obartu...@gmail.com mailto:obartu...@gmail.com wrote: check knn search, http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf http://www.sai.msu.su/%7Emegera/postgres/talks/pgcon-2010-1.pdf On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla itishree.su...@gmail.com mailto:itishree.su...@gmail.com wrote: Deal all, In my project, there is a requirement to implement proximity search feature. We are running a mobile app, for which proximity search is require. Can any one guide me how i can achieve this using postgis, or is there any other way i can achieve this. We are using postgresql 9.2. Thanks in advance . Regards, Itishree Pleas bottom post like I am here, it is the norm for these mailing lists. Some comments interspersed are also okay. It makes it easier for people to see the context of what you are saying. Thanks, Gavin
[GENERAL] expert : SRF returning double[]
Hey list, another tricky C function interface problem : How to write a set returning function, that returns for each row an array? it seems like the main function SRF_RETURN_NEXT takes Datum and so I can't use PG_RETURN_ARRAYTYPE_P(). Shall I encapsulate the array into a composite field (only 1 field : the double array) I looked a lot for an example wihtout success. Help greatly appreciated, thanks, Cheers, Rémi-C
Re: [GENERAL] expert : SRF returning double[]
another auto-answer : Suprisingly , result = construct_array(...) SRF_RETURN_NEXT(funcctx, PointerGetDatum(result)); But Datum memory must be allocated Cheers, Rémi-C 2014/1/16 Rémi Cura remi.c...@gmail.com Hey list, another tricky C function interface problem : How to write a set returning function, that returns for each row an array? it seems like the main function SRF_RETURN_NEXT takes Datum and so I can't use PG_RETURN_ARRAYTYPE_P(). Shall I encapsulate the array into a composite field (only 1 field : the double array) I looked a lot for an example wihtout success. Help greatly appreciated, thanks, Cheers, Rémi-C
Re: [GENERAL] Need Help to implement Proximity search feature
On 1/16/2014 1:52 AM, itishree sukla wrote: we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. for determining closest proximity, you probably don't need accurate spherical earth distance, simple cartesian distance is good enough as you're only interested in relative values. for this, you can use the built in postgresql POINT data type, and the - distance operator. -- john r pierce 37N 122W somewhere on the middle of the 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] Any freeware graphic display of DDL software available?
Hi, try DBVisualizer -- Regards, Bartek
Re: [GENERAL] PostgreSQL with ZFS on Linux
Hi, If you really want ZFS, I would highly recommend looking into FreeBSD (Postgresql works great on it) or if you want to stick with Linux, look into mdadm with LVM or some other filesystem solution. If you want to use ZFS because of its features, take a look at btrfs. It provides a lot of the stuff supported by ZFS with usually better performance on linux - and since the last few kernel revisions it is finally in a state where I would dare to use it in production. If you need highest performance, don't use a copy-on-write filesystem like ZFS or btrfs, stick to ext4 or XFS ;) Regards, Clemens -- 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] Any freeware graphic display of DDL software available?
I use this script on an Ubuntu system: #!/bin/bash set -eu postgresql_autodoc -d example_dev -u example_dev -h localhost --password= dot -Tpng -o example-schema.png example_dev.dot dot -Tpdf -o example-schema.pdf example_dev.dot That gives you a schema diagram in pdf, png, dia, and dot format, including foreign keys. It also exports the table and column info as html and xml. I'm pretty sure you can install postgresql_autodoc with apt-get, but I don't recall for sure now. Good luck! Paul On Thu, Jan 16, 2014 at 1:09 PM, Bartosz Dmytrak bdmyt...@gmail.com wrote: Hi, try DBVisualizer -- Regards, Bartek -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for settings/configuration for FASTEST reindex on idle system.
On Thu, Jan 9, 2014 at 2:03 PM, Jeff Amiel becauseimj...@yahoo.com wrote: I have a maintenance window coming up and using pg_upgrade to upgrade from 9.2.X to 9.3.X. As part of the window, I’d like to ‘cluster’ each table by its primary key. After doing so, I see amazing performance improvements (probably mostly because of index bloat - but possibly due to table fragmentation) Since you seem to have a test environment where you can so such things, you can try first reindexing, and then clustering, to so which step is the important one to get the performance improvement. If it is the reindexing that is really the key, you could get that benefit outside of the maintenance window by building a new index with the same columns concurrently, then dropping the old one, doing it one index at a time. If the cluster is the key part, that is hard to move outside a maintenance window. That being said, I have a single table that is blowing my window - at 140 million rows (28 gig in size with 75 gig worth of indexes), this bad boy is my white whale. There are 10 indexes (not including the primary key). Yes - 10 is a lot - but I’ve been monitoring their use (most are single column or partial indexes) and all are used. That being said, I’ve been reading and experimenting in trying to get a cluster of this table (which re-indexes all 10/11 indexes) to complete in a reasonable amount of time. There are lots of settings and ranges to chose from and while my experiments continue, I was looking to get some input. Lowest I have gotten for clustering this table is just under 6 hours. I am familiar with pg_reorg and it’s sibling pg_repack - but they call the base postgresql reindex functions underneath - and I have learned by using ‘verbose’ that the actual clustering of the table is quick - it’s the reindexing that is slow (It’s doing each reindex sequentially instead of concurently) PostgreSQL 9.3.2 on x86_64-pc-solaris2.11, compiled by gcc (GCC) 4.5.2, 64-bit 500 gig of ram 2.7gig processors (48 cores) Shared buffers set to 120gig Maintenance work men set to 1gig work men set to 500 meg Things I have read/seen/been told to tweak… fsync (set to off) I see little point in that. You are putting your data at serious risk, and bulk index creation shouldn't be doing much fsyncing anyway. setting wal_level to minimal (to avoid wal logging of cluster activity) That can be a big win in some cases, if it is compatible with your backup policy. But I don't know if cluster is actually one of those cases. bumping up maintenance work men (but I’ve also seen/read that uber high values cause disk based sorts which ultimately slow things down) I don't understand that advice. If it is big enough, you can *avoid* disk bases sorts by doing them in RAM, which should be faster, provided you actually have enough RAM so that you don't swap. However, there are a variety of things which limit how much memory a sort could actually use (most of which will be removed in 9.4) so you might not get in-RAM sorts no matter how much you crank up maintenance_work_mem. You can turn on trace_sort to see whether your sorts are in RAM or on disk, and how much memory they are using. Tweaking checkpoint settings (although with wal_level set to minimal - I don’t think it comes into play) Measure it--how often does it cycle through checkpoints during your test CLUSTER? any good suggestions for lighting a fire under this process? If worse comes to worse, I can vacuum full the table and reindex each index concurrently - but it won’t give me the benefit of having the tuples ordered by their oft-grouped primary key. If you vacuum full, it will rebuild the indexes for you automatically, so after that there is no point in doing a manual reindex. You could drop them manually before hand, and then build them manually afterward, but you can do that whether the central activity is a CLUSTER or a VACUUM FULL. If you want to do this in parallel, then you need to keep in mind that maintenance_work_mem needs to be small enough to fit all of the concurrent builds, and your IO system needs to accomodate all of that traffic. Cheers, Jeff
Re: [GENERAL] Any freeware graphic display of DDL software available?
It doesn't appear that DBVisualizer does an ER type diagram, which is what I really need. Thanks, Susan On Thu, Jan 16, 2014 at 1:09 PM, Bartosz Dmytrak bdmyt...@gmail.com wrote: Hi, try DBVisualizer -- Regards, Bartek
Re: [GENERAL] Any freeware graphic display of DDL software available?
On Thu, Jan 16, 2014 at 2:57 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: It doesn't appear that DBVisualizer does an ER type diagram, which is what I really need. DBSchema outputs ER diagrams: http://www.dbschema.com/database-er-diagrams.html I think the paid version is about $300 IIRC.
Re: [GENERAL] Any freeware graphic display of DDL software available?
What are the dot lines for? They don't seem to work on my Linux installation. I tried adding them to the initial line, and I see no .pdf output. I just see: Producing testdb.dia from /usr/share/postgresql_autodoc/dia.tmpl Producing testdb.dot from /usr/share/postgresql_autodoc/dot.tmpl Producing testdb.html from /usr/share/postgresql_autodoc/html.tmpl Producing testdb.neato from /usr/share/postgresql_autodoc/neato.tmpl Producing testdb.xml from /usr/share/postgresql_autodoc/xml.tmpl Producing testdb.zigzag.dia from /usr/share/postgresql_autodoc/zigzag.dia.tmpl The .html file just shows a table-like representation of each individual table, which is no real use to me, as I need the relationships between tables to show graphically. What do I do with a .dot or .dia formatted file? Thanks, Susan On Thu, Jan 16, 2014 at 2:06 PM, Paul Jungwirth p...@illuminatedcomputing.com wrote: I use this script on an Ubuntu system: #!/bin/bash set -eu postgresql_autodoc -d example_dev -u example_dev -h localhost --password= dot -Tpng -o example-schema.png example_dev.dot dot -Tpdf -o example-schema.pdf example_dev.dot That gives you a schema diagram in pdf, png, dia, and dot format, including foreign keys. It also exports the table and column info as html and xml. I'm pretty sure you can install postgresql_autodoc with apt-get, but I don't recall for sure now. Good luck! Paul On Thu, Jan 16, 2014 at 1:09 PM, Bartosz Dmytrak bdmyt...@gmail.com wrote: Hi, try DBVisualizer -- Regards, Bartek -- _ Pulchritudo splendor veritatis.
Re: [GENERAL] Any freeware graphic display of DDL software available?
On 01/16/2014 03:12 PM, Susan Cassidy wrote: What are the dot lines for? They don't seem to work on my Linux installation. I tried adding them to the initial line, and I see no .pdf output. I just see: Producing testdb.dia from /usr/share/postgresql_autodoc/dia.tmpl Producing testdb.dot from /usr/share/postgresql_autodoc/dot.tmpl Producing testdb.html from /usr/share/postgresql_autodoc/html.tmpl Producing testdb.neato from /usr/share/postgresql_autodoc/neato.tmpl Producing testdb.xml from /usr/share/postgresql_autodoc/xml.tmpl Producing testdb.zigzag.dia from /usr/share/postgresql_autodoc/zigzag.dia.tmpl The .html file just shows a table-like representation of each individual table, which is no real use to me, as I need the relationships between tables to show graphically. What do I do with a .dot or .dia formatted file? http://www.rbt.ca/autodoc/output.html Where GraphViz = dot. Thanks, Susan -- Adrian Klaver adrian.kla...@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] Any freeware graphic display of DDL software available?
On 1/16/2014 3:12 PM, Susan Cassidy wrote: What do I do with a .dot or .dia formatted file? not sure about .DOT, but .DIA is probably for the Dia drawing program, which is a simple Vizio like program, free open source. -- john r pierce 37N 122W somewhere on the middle of the 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] Breakpoints are not triggered in analyze.c (debugging Postgresql in Visual studio)
Hi Tom, I'm debugging the /backend/commends/analyze.c. I've set up a Function breakpoint in visual studio,but seems no luck, T_T 2014-01-17 ygnhzeus 发件人：Tom Lane t...@sss.pgh.pa.us 发送时间：2014-01-16 23:31 主题：Re: [GENERAL] Breakpoints are not triggered in analyze.c (debugging Postgresql in Visual studio) 收件人：Felix.徐ygnhz...@gmail.com 抄送：pgsql-generalpgsql-general@postgresql.org =?GB2312?B?RmVsaXgu0Ow=?= ygnhz...@gmail.com writes: I've set up a developing environment on my windows using Visual Studio 2012, everything works fine, except that the breakpoints set in analyze.c are not triggered in debug mode (breakpoints in main.c and some initialization code worked well), and I'm sure that line has been executed since my own messages have been printed on the console. Does anyone have any experience debugging postgresql in Visual Studio? There are two different source files named analyze.c; I wonder which one the debugger thinks you are talking about ... In gdb, the solution for this is to always start by setting a breakpoint by function name. Once you're stopped in a particular source file, gdb will assume that that file is meant by b linenumber references. Perhaps the same kind of trick will work with VS. regards, tom lane
Re: [GENERAL] Any freeware graphic display of DDL software available?
On Thu, Jan 16, 2014 at 10:45 AM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: Is there any free or cheap software that will read in DDL and output a graphic display of it? Preferably showing links for foreign keys. I know about Erwin, but it is too expensive. I want to give a shout out for schemaspy . I think it's about the best ERD tool out there; the graphviz relationship mapping 'just works' and you have to spend zero time mucking around with the tool post extraction which is a critical flaw with many ERD tools. It's aware but there are undocumented switches to remove the ads. My experience is that ERD tools that require manual steps of any kind tend to become quickly out of date and useless. 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] Any freeware graphic display of DDL software available?
Mogwai: http://sourceforge.net/projects/mogwai/?source=directory Needs JDBC drivers, but most database systems, including Postgres have one. It is a little fiddly setting it up, but it seems to work with lots of data sources. Make sure the PosgreSQL jdbc driver is in the class path before you use it. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Susan Cassidy Sent: Thursday, January 16, 2014 2:57 PM To: Bartosz Dmytrak Cc: pgsql-general@postgresql.org; karsten.hilb...@gmx.net; s_ju...@yahoo.com Subject: Re: [GENERAL] Any freeware graphic display of DDL software available? It doesn't appear that DBVisualizer does an ER type diagram, which is what I really need. Thanks, Susan On Thu, Jan 16, 2014 at 1:09 PM, Bartosz Dmytrak bdmyt...@gmail.commailto:bdmyt...@gmail.com wrote: Hi, try DBVisualizer -- Regards, Bartek
Re: [GENERAL] PostgreSQL with ZFS on Linux
On Thu, Jan 16, 2014 at 6:09 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: http://www.unix-experience.fr/2013/2451/ FreeBSD is also a very mature platform for ZFS/postgresql. It is more mature than Linux for sure, but still not up to par with Solaris for some features. See this comment for example: http://www.dslreports.com/forum/r27269032-. I do like FreeBSD very much, but it seems to me that if I want to use ZFS to its fullest, I should go with OpenIndiana. In the benchmark you link, ZFS+opti (orange line, physical machine benchmark) performs well because it has sync=disabled, which is risky to say the least ... On 16/01/2014 11:57, Sébastien Lorion wrote: On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion s...@thestrangefactory.com wrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien FYI, a recent (Sept. 2013) presentation I found about using ZoL in production (albeit, not with PostgreSQL) and the current status of the project: http://lanyrd.com/2013/linuxcon-north-america/scqmfb/ -- Achilleas Mantzios Head of IT DEV IT DEPT Dynacom Tankers Mgmt
Re: [GENERAL] PostgreSQL with ZFS on Linux
On Thu, Jan 16, 2014 at 6:34 AM, Alban Hertroys haram...@gmail.com wrote: On 16 January 2014 12:09, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: http://www.unix-experience.fr/2013/2451/ FreeBSD is also a very mature platform for ZFS/postgresql. More mature than on Linux even, as far as I know. If I had to choose an OS to use ZFS with, I'd go with either FreeBSD or Solaris. That said, I am biased to FreeBSD anyway; the only Linux installation that I own is the one in my Android phone, while I own several FreeBSD systems. On Thu, Jan 16, 2014 at 4:22 AM, Sébastien Lorion s...@thestrangefactory.com wrote: Hello, Since ZFS on Linux (http://zfsonlinux.org/) has been declared production ready last March (v0.6.1), I am curious if anyone is using it with PostgreSQL on production servers (either main or backup) and if so, what is their experience so far ? Thank you, Sébastien I do not consider ZFS an ideal file-system for databases. I'm not an expert on ZFS, but there are two features in ZFS that I think particularly make it less suitable for database use. One reason is that ZFS, as I understand it, is a log-structured file-system. That means that changes to files always go to the end of the file-system. If that file is a large frequently updated database table, records are going to be far apart and in fairly random order. That could seriously hurt performance. Secondly, with ZFS you need to reserve a significant amount of memory for the ZIL. That is memory that is not available to your database. Don't take my word for it, but I think the above points are worth investigating as is finding some file-system bench- marks where ZFS gets compared to, for example, UFS2 (FreeBSD), Ext4fs (Linux). Of course, the other side of the coin is ZFS's excellent flexibility. Cheers, Alban Hertroys -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Those are very good points, but from my own tests and recent TPC benchmarks I saw on the net (sorry, don't have the links anymore), using SSD makes them not/less an issue. As you say, ZFS flexibility and ease of maintenance trumps many cards. Also, something worth pointing out and which may be counter-intuitive is that using ZFS compression can actually speed things up: http://citusdata.com/blog/64-zfs-compression Sébastien
Re: [GENERAL] PostgreSQL with ZFS on Linux
On Thu, Jan 16, 2014 at 4:42 PM, Clemens Eisserer linuxhi...@gmail.comwrote: Hi, If you really want ZFS, I would highly recommend looking into FreeBSD (Postgresql works great on it) or if you want to stick with Linux, look into mdadm with LVM or some other filesystem solution. If you want to use ZFS because of its features, take a look at btrfs. It provides a lot of the stuff supported by ZFS with usually better performance on linux - and since the last few kernel revisions it is finally in a state where I would dare to use it in production. If you need highest performance, don't use a copy-on-write filesystem like ZFS or btrfs, stick to ext4 or XFS ;) Regards, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Do you have any personal experience with BTRFS for a couple of weeks in production or any official statement/case study ? On the FAQ, it says it is still experimental (https://btrfs.wiki.kernel.org/index.php/FAQ), though it may just be outdated. There is also these two links that would make me very cautious (as I am with ZFS on Linux, mind you): http://www.anchor.com.au/blog/2013/04/the-btrfs-backup-experiment/ http://arstechnica.com/civis/viewtopic.php?f=16t=1221177 Sébastien
Re: [GENERAL] PostgreSQL with ZFS on Linux
On Thu, Jan 16, 2014 at 11:14 PM, Sébastien Lorion s...@thestrangefactory.com wrote: On Thu, Jan 16, 2014 at 4:42 PM, Clemens Eisserer linuxhi...@gmail.comwrote: Hi, If you really want ZFS, I would highly recommend looking into FreeBSD (Postgresql works great on it) or if you want to stick with Linux, look into mdadm with LVM or some other filesystem solution. If you want to use ZFS because of its features, take a look at btrfs. It provides a lot of the stuff supported by ZFS with usually better performance on linux - and since the last few kernel revisions it is finally in a state where I would dare to use it in production. If you need highest performance, don't use a copy-on-write filesystem like ZFS or btrfs, stick to ext4 or XFS ;) Regards, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Do you have any personal experience with BTRFS for a couple of weeks in production or any official statement/case study ? On the FAQ, it says it is still experimental (https://btrfs.wiki.kernel.org/index.php/FAQ), though it may just be outdated. There is also these two links that would make me very cautious (as I am with ZFS on Linux, mind you): http://www.anchor.com.au/blog/2013/04/the-btrfs-backup-experiment/ http://arstechnica.com/civis/viewtopic.php?f=16t=1221177 We're looking at it for LedgerSMB hosting (with PostgreSQL, we are currently using XFS). So far we are liking what we are seeing. We wouldn't be using it for PostgreSQL backups, but the general sense is that the developers are very, very conservative about making guarantees of stability and so far we haven't seen any indication that experimental means anything other than developers nervous about calling it stable. This being said, we aren't very far into our evaluation yet and our view could change. -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more.shtml
