Posted
about 18 hours
ago
With pg_upgrade gaining wide acceptance and the Firefox browser implementing a
continuous upgrade model, it
might be time to consider auto-upgrades for Postgres. Consider this possible future output:
$ psql test
psql
... [More]
(12.3.7)
Postgres is up to date
Type "help" for help.
test=>
Continue Reading » [Less]
Posted
1 day
ago
Slides from my 2012 PGCon presentation Making your Own Maps are now available
The presentation covered
Common reasons people render their own maps
Where to get OpenStreetMap data and how to load it into your PostGIS
... [More]
database
How to use Tilemill to design your own map style
How to render map tiles, both statically and dynamically
How to use OpenLayers to display your map
The presentation was recorded. I will update this post when the recording comes online.
I’ve been a regular attendee of PGCON since the first year it was held in Ottawa. Like past years I enjoyed the conference and I would like to thank Dan Langille for putting together another first-rate conference. My favourite part of PostgreSQL conferences is meeting and reconnecting with users people in the community.
[Less]
Posted
1 day
ago
Since PostgreSQL 9.2 is in beta now, I've been using some generously donated cloud server time on GoGrid to test various features in the new release. One of the ones which is working brilliantly is the new, more verbose logging for
... [More]
Autovacuum.
Greg Smith and Noah Misch added additional information to the logging you get when you set log_autovacuum_min_duration. This includes information on the work done by autovacuum (buffers, pages and tuples read and written), as well as information on when autovacuum "skips" because it can't get a lock. Here's a sample:
LOG: automatic analyze of table "bench2.public.pgbench_tellers" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG: automatic vacuum of table "bench2.public.pgbench_branches": index scans: 0
pages: 0 removed, 2 remain
tuples: 114 removed, 200 remain
buffer usage: 44 hits, 0 misses, 3 dirtied
avg read rate: 0.000 MiB/s, avg write rate: 45.334 MiB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG: automatic analyze of table "bench2.public.pgbench_branches" system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec
LOG: automatic analyze of table "bench2.public.pgbench_tellers" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
This will go a long way towards helping users troubleshoot autovacuum in the future. Great work, Greg and Noah! [Less]
Posted
1 day
ago
One new welcome feature in PostgreSQL 9.2 is the native json support and companion row_as_json and array_as_json functions. PostGIS also has a json function for outputting geographies and geometries in GeoJson format which is almost a standard in
... [More]
web mapping.
Here is an example of how you'd use the new feature - create our test table
CREATE TABLE test(gid serial primary key, title text, geog geography(Point,4326));
INSERT INTO test(title, geog) VALUES('My neck of the woods', ST_GeogFromText('POINT(-71.057811 42.358274)'));
INSERT INTO test(title, geog) VALUES('some other neck of the woods', ST_GeogFromText('POINT(42.358274 -71.057811 )'));
Now with a command like this I can output all of my data as a single json object.
SELECT array_to_json(array_agg(t))
FROM test As t;
But there is a tincy little problem. My output looks like this:
[{"gid":1,"title":"My neck of the woods","geog":"0101000020E61000005796E82CB3C3
51C0E98024ECDB2D4540"}
,{"gid":2,"title":"some other neck of the woods","geog":"0
101000020E6100000E98024ECDB2D45405796E82CB3C351C0"}]
But to follow the geoJson standard, my geography object should output like this:
{"type":"Point","coordinates":[-71.057811000000001,42.358274000000002]}
Continue reading "PostgreSQL 9.2 native json type support" [Less]
Posted
1 day
ago
Pg_upgrade
(documentation,
presentation) was
created in 1998 to allow
in-place major version upgrades of Postgres. It was written as a shell script, as were many of our command-line administration tools at
the time. (Postgres
... [More]
didn't support Windows natively until 2005). In
those early years, Postgres changed its on-disk format frequently in major releases, causing pg_upgrade to be only occasionally useful for
major version upgrades (6.5,
7.1, disabled in
2002).
Postgres feature additions soon made the shell script method unworkable, and binary-upgrades were unsupported for many years. In 2006,
EnterpriseDB took on the job of rewriting pg_upgrade in
C, a task that had been discussed many years on the mailing lists — this email
thread, titled "Upgrading rant", give you an idea of the tone of
previous discussions.
In 2009, EnterpriseDB realized that a successful binary-upgrade utility had to be community-developed, so they changed the license from
GPL to BSD and encouraged me to work with the community to improve the tool. In 2010, pg_upgrade was
added to the official Postgres 9.0 release. Postgres 9.1 saw
only performance improvements.
Postgres 9.2 will improve the user interface and error
reporting. It is significant that pg_upgrade has not required large changes to support new major releases, only minor improvements.
Continue Reading » [Less]
Tagged as
database odbc dbms rdbms clustering jdbc dbi engine server transactions postgresql gis replication posix sql relational xml database_server
BlackRay Data Engine
Firebird
HyperSQL Database Engine
Ingres Database
MySQL