Josh Berkus (pgExperts) - 9- Andrew Dunstan (pgExperts) - 8
- Hubert 'depesz' Lubaczewski (OmniTI) - 5
- Bruce Momjian (EnterpriseDB) - 4
- Joel Jacobson - 4
- Leo Hsu and Regina Obe - 4
- Michael Paquier (VMware) - 4
- Chris Travers - 3
- Shaun M. Thomas - 3
- Denish Patel (OmniTI) - 2
- Hans-Juergen Schoenig (Cybertec) - 2
- Ian Barwick - 2
- Raghavendra Rao (EnterpriseDB) - 2
- Selena Deckelmann - 2
- Christophe Pettus (pgExperts) - 1
- Dan Langille - 1
- David Wheeler (pgExperts) - 1
- Dimitri Fontaine (2ndQuadrant) - 1
- Emanuel Calvo - 1
- Eyðun Nielsen - 1
- pgExperts - 19
- OmniTI - 7
- EnterpriseDB - 6
- VMware - 5
- 2ndQuadrant - 2
- Cybertec - 2
- StormDB - 2
- EndPoint - 1
- Follow @planetpostgres on Twitter for the latest updates!
- Policy for being listed on Planet PostgreSQL.
- Add your blog to Planet PostgreSQL.
- List of all subscribed blogs.
- Manage your registration.
- Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.
One of the
clients of OmniTI requested
help to provide sample application to insert
JSON data into Postgres using Java JDBC driver . I’m not Java expert
so it took a while for me to write a simple java code to insert
data. TBH, I took help to write test application from one of our
Java engineers at OmniTI. Now, test application is ready and next
step is to make it work with JSON datatype ! After struggling a
little to find out work around for string escaping in JAVA code, I
stumbled upon data type issue!
Here is the test application code to connect to
my local Postgres installation and insert JSON data into sample
table:
postgres=# \d sample
Table "public.sample"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
data | json |
denishs-MacBook-Air-2:java denish$ java -cp $CLASSPATH
PgJSONExample
-------- PostgreSQL JDBC Connection Testing ------------
PostgreSQL JDBC Driver Registered!
You made it, take control your database now!
Something exploded running the insert: ERROR: column "data" is of
type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 42
After some research , I
found out that there is no standard JSON type on java side
so adding support for json to postgres jdbc is not straight forward
! StackOverflow
answer helped me for testing out the JSON datatype
handling at psql level. As Craig mentioned in the answer that
the correct way to solve this problem is to write a custom Java
mapping type that uses the JDBC setObject method. This can be a
tricky though. A simpler workaround is to tell PostgreSQL to
cast implicitly from text to json:
postgres=# create cast (text as json) without function as
implicit;
CREATE CAST
The WITHOUT FUNCTION clause is used because text and json have
the same on-disk and in-memory representation, they’re basically
just aliases for the same data type. AS IMPLICIT tells PostgreSQL
it can convert without being explicitly told to, allowing things
like this to work:
postgres=# prepare test(tex
One of the things that really frustrated me about the KNN GIST distance box box centroid operators that came in PostgreSQL 9.1 and PostGIS 2.0 was the fact that one of the elements needed to be constant to take advantage of the index. In PostGIS speak, this meant you couldn't put it in the FROM clause and could only enjoy it in one of two ways.
Continue reading "KNN GIST with a Lateral twist: Coming soon to a database near you"
Having recently posted some thoughts on Shaun Thomas' " "PostgreSQL Backup and Restore How-to" review ", Packt asked me if I'd like to review the new " Instant PostgreSQL Starter " by Daniel K. Lyons and kindly provided me with access to the ebook version. As I'm happily in a situation where I may need to introduce PostgreSQL to new users, I was interested in taking a look and here's a quick overview.
It follows the same "Instant" format as the backup booklet, which I quite like as it provides a useful way of focussing on particular aspects of PostgreSQL without being bogged down in reams of tl;dr documentation. " Instant Pg Starter " is divided into three sections:
Installation Quick start – creating your first table Top 9 features you need to know aboutIt occurs to me I forgot to congratulate the winners of the free ebooks. So without further adieu:
- SAB, who seems to host a nice blog geared toward server administration.
- Stephan, who’s looking to improve existing strategies.
- Jeff and his growing PostgreSQL cluster.
- Pierre, who apparently has an experimental PostgreSQL backend for MySQL. Interesting.
Congrats to the winners. But more, I call upon them to pay it
forward by contributing to the community, either by corresponding
with the excellent PostgreSQL
mailing lists, or maybe submitting a patch or two to the code.
There’s a lot of ground to cover, and more warm bodies always
helps. 
Thanks again, everyone!
This is not a new goal, in some ways. Improving installation, one of our previous 5 goals, was really about improving the experience for new users. But the new user experience goes beyond installation now, and competition has "raised the bar". That is, we matched MySQL, but now that's not good enough; we need to match the new databases. It should be as easy to get started on a dev database with PostgreSQL as it is with, for example, Redis. Let me give you a summary of the steps to get up, running, and developing an application in the two platforms:
Redis:
- install Redis, either from packages or multiplatform binaries. No root access is required for the binaries.
- read a 1-page tutorial
- run redis-server
- run redis-cli or install drivers for your programming language
- start developing
- when your app works, deploy to production
- in production, tune how much RAM Redis gets.
- install PostgreSQL from packages or the one-click installer. Root/Admin access is usually required.
- search the documentation to figure out how to get started.
- figure out whether or not your packages automatically start Postgres. If not, figure out how to start it. This may require root access.
- Install drivers for your programming language.
- Figure out how to connect to PostgreSQL. This may require making changes to configuration files.
- Read more pages of documentation to learn the basics of PostgreSQL's variety of SQL, or how to program an ORM which works with PostgreSQL.
- Start developing.
- Deploy to production.
- Read 20 pages of documentation, plus numerous blogs, wiki pages and online presentations in order to figure out how to tune PostgreSQL.
- Tune PostgreSQL for production workload. Be unsure if you've done it right.
(note: this series of articles is my personal opinion as a PostgreSQL core team member)
The benefits to having some kind of parallel query are obvious to most users and developers today. Mostly, people tend to think of analytics and parallel query across terabyte-sized tables, and that's definitely one of the reasons we need parallel query. But possibly a stronger reason, which isn't much talked about, is CPU-heavy extensions -- chief among them, PostGIS. All of those spatial queries are very processor-heavy; a location search takes a lot of math, a spatial JOIN more so. While most users of large databases would like parallel query in order to do things a bit faster, PostGIS users need parallism yesterday.
Fortunately, work on parallelism has already started. Even more fortunately, parallel query isn't a single monumental thing which has to be done as one big chunk; we can add parallelism piecemeal over the next few versions of Postgres. Rougly, parallel query breaks down into parallelizing all of the following operations:
- Table scan
- Index scan
- Bitmap scan
- In-memory sort
- On-disk sort
- Hashing
- Merge Join
- Nested loop join
- Aggregation
- Framework for parallel functions
Most of these features can be worked on independently, in any order -- dare I say, developed in parallel? Joins probably need to be done after sorts and scans, but that's pretty much it. Noah Misch has chosen to start with parallel in-memory sort, so you can probably expect that for version 9.4.
There’s a fantastic set of blog posts about distributed databases and network partitioning, starting with this post explaining the perils of trying to “communicate with someone who doesn’t know you’re alive.”
The next post is about Postgres and 2-phase commit. And there are four additional posts in the series.
The whole series worth reading for anyone interested in data
stores, consistency and Postgres! 
In our previous article we went through describing what retention policies are and how they can be enforced on your PostgreSQL server backups with Barman 1.2. In this post, we will go through the configuration aspects.
For the sake of simplicity, we assume a typical scenario which involves taking full backups once a week through the “barman backup” command. Suppose you want to automatically keep the latest 4 backups and let Barman automatically delete the old ones (obsolete).
The main configuration option for retention policies in Barman is “retention_policy” which can be defined both at global or server level. If you want all your servers by default to keep the last 4 periodical backups, you need to add in the general section of Barman’s configuration file the following line:
[barman] ... // General settings retention_policy: REDUNDANCY 4
When the next “barman cron” command is executed (every minute if you installed Barman using RPMs or Debian/Ubuntu packages), Barman checks for the number of available full periodical backups for every server, order them in descending chronological order (from the most recent to the oldest one) and deletes backups from the 5th position onwards.
In case you have several servers backed up on the same Barman host and you want to differentiate the retention policy for a specific server, you can simply edit that server configuration section (or file, see “Managing the backup of several PostgreSQL servers with Barman“) and define a different setting:
[malcolm] description = Malcolm Rocks ssh_command = ssh malcolm conninfo = host=malcolm port=5432 user=postgres dbname=postgres retention_policy: REDUNDANCY 8
However, Barman allows systems administrators to manage retention policies based on time, in terms of recovery window and point of recoverability. For example, you can set another server to allow to recover at any point in time in the last 3 months:
[angus] description = Angus Rocks ssh_command = ssh angus conninfo = host=angus port=5432 user=postgres dbname=postgres retention
In addition to pluggable storage, a second thing we really need is a pluggable parser interface. That is, it should be possible to generate a statement structure, in binary form, and hand that off to libpq for execution. There was recently some discussion about this on -hackers.
If there were a way to hand off expression trees directly to the planner, then this would allow creating extensions which actually had additional syntax, without having to fork PostgreSQL. This would support most of those "compatibility" extensions, as well as potentially allowing extensions like SKYLINE OF which change SQL behavior.
It would also help support PostgreSQL-based clustered databases, by allowing all of the parsing for a particular client to happen on a remote node and get passed to the clustered backends. The pgPool2 project has asked for this for several years for that reason.
More intriguingly, it would allow for potentially creating an "ORM" which doesn't have to serialize everything to SQL, but can instead build expression trees directly based on client code. This would both improve response times, and encourage developers to use a lot of PostgreSQL's more sophisticated features since they could access them directly in their code.
Taking things a step further, we could extend this to allow users to hand a plan tree directly to the executor. This would fix things for all of the users who actually need query hints (as opposed to those who think they need them), as well as taking efficiency a step beyond cached plans.
There are a lot of reasons this would be just as difficult to do as pluggable storage. Currently parsing depends on a context-dependant knowledge of system catalogs, inc
I’ve got a Beer & Tell to give about alembic. Alembic is a migration tool that works with SQLAlchemy. I’m using it for database migrations with PostgreSQL.
So, here’s what I want to say today:
- Written by SQLAlchemy wiz Mike Bayer
- Here’s the tutorial. Socorro is now using alembic in production with SQLAlchemy 0.6.x. I’m hoping to get us upgraded to 0.8.x soon.
- Here’s what running an upgrade in production for Socorro looks like. Awesome right?
- Here’s what a migration looks like.
- Here’s a configuration file.
- Generating a migration from the command line might look
something like:
alembic revision -m "bug XXXXXX Add a new table" --autogenerate
The most difficult thing to deal with so far are the many User Defined Functions that we use in Socorro. This isn’t something that any migration tools I tested deal well with.
Happy to answer questions! And I’ll see about making a longer talk about this transition soon.
Do you use PostgreSQL and truly believe it’s “the world’s most advanced open source database” and that its upcoming 9.3 release will make it even more awesome?
Do you also use Python and believe it’s “an easy to learn, powerful programming language” with “elegant syntax” that makes it an ideal language for developing applications and tools around PostgreSQL, such as Pyrseas?
Then we could use your help. For starters, we want to add support for the MATERIALIZED VIEWs and EVENT TRIGGERs coming up in PG 9.3.
We have also been requested to add the capability to load and maintain “static data” (relatively small, unchanging tables) as part of yamltodb, so that it can be integrated more easily into database version control workflows.
And for the next release, Pyrseas 0.7, we’d like to include the first version of the database augmentation tool which will support declarative implementation of business logic in the database–starting off with audit trail columns. Some work has been done on this already, but it needs integration with the current code and tests.
Or perhaps coding is not your forte, but you’re really good at explaining and documenting technical “stuff”. Then you could give us a hand with revamping the docs, maybe writing a tutorial so that users have a smooth ride using our tools.
Or maybe you have your own ideas as to how improve the PostgreSQL version control experience. We’d love to hear those too.
If you’d like to help, you can fork the code on GitHub, join the mailing list and introduce yourself, or leave a comment below.
Filed under: PostgreSQL, Python, Version control
Backup and recovery in Postgres-XC has some parallels to PostgreSQL, but with its own wrinkles.
Considering the amount of venture capital money poured into these forks, that's a big loss of feature contributions from the community. Especially when the startup in question gets bought out by a company who buries it or loots it for IP and then kills the product.
More importantly, we have a number of people who would like to do something interesting and substantially different with PostgreSQL storage, and will likely be forced to fork PostgreSQL to get their ideas to work. Index-organized tables, fractal trees, JSON trees, EAV-optimized storage, non-MVCC tables, column stores, hash-distributed tables and graphs all require changes to storage which can't currently be fit into the model of index classes and blobs we offer for extensibility of data storage. Transactional RAM and Persistent RAM in the future may urge other incompatible storage changes.
As a community, we want to capture these innovations and make them part of mainstream Postgres, and their users part of the PostgreSQL community. The only way to do this is to have some form of pluggable storage, just like we have pluggable function languages and pluggable index types.
The direct way to do this would be to refactor our code to replace all direct manipulation of storage and data pages with a well-defined API. This would be extremely difficult, and would produce large performance issues in the first few versions. It would, however, also have the advantage of allowing us to completely solve the binary upgrade of page format changes issue.
A second a
In earlier releases, if there's any data corruption block on disk it was silently ignored until any pointer arrives on it or some wrong results shown by the queries. Now, data corruption detected beforehand by triggering WARNING message instead of silently using or waiting for hit on the corrupted block.
Disk page checksums feature implementation is unique, its not plug-able like EXTENSIONs its selectable feature. That's if you need your database should be under monitory umbrella of data corruption then it should be enabled at the time of cluster initialization not on existing or running cluster. Below's the example how it works.
Initialize the cluster with checksums:
initdb [OPTION]... [DATADIR]Now, any data corruption found will be notified as below:
........
-k, --data-checksums use data page checksums
initdb -D data_directory -k
postgres=# select * from corruption_test;In earlier version,just an error message.
WARNING: page verification failed, calculated checksum 63023 but expected 48009
ERROR: invalid page in block 0 of relation base/12896/16409
postgres=# select * from corruption_test where id=1;That's cool right....
ERROR: invalid page header in block 0 of relation base/12870/18192
So, how do you know whether disk page checksums enabled on the cluster or not ?
As of now, there's no pg_catalog to store such information or any files created in the $PGDATA directory, only pg_control file will hold that information. Using pg_controldata utility you can know about it.
$ export PGDATA=/usr/local/pg93beta/dataSome points on Disk page checksums:
$ pg_controldata
....
....
....
Data page checksum version: 1
1. Temp tables are excluded from checksums checks.
2. There's performance overhead if checksums enabled as per the PG documentation.
3. Once enabled checksums on a cluster cannot be rolled back.
Thanks
Raghav
The Call for Papers for DjangoCon US 2013 is now open.
Second, my article on the PostgreSQL 9.3 Beta is up at LWN.net (subscription required, or wait 2 weeks).
I've been thinking a bit about his complaint, and I think this will be doable. What we'll need is a new table option that specifies the key and designates the table as one sourced from a single key rather than a range of keys. Say we call this option singleton_key. Then we might do something like:
This option would be incompatible with the tablekeyset and tablekeyprefix options. If given, the key won't be looked up at all. We would simply use the given key and return the corresponding list of values. That would make selecting from such a table faster - possibly lots faster. For scalars, sets and lists, the table would have one column. In the case of a scalar there would only be one row. For zsets, it would have two columns - one for the value and one for the score. Finally, for hashes it would have two, one for the property and one for the value.CREATE FOREIGN TABLE hugeset (value text)
SERVER localredis
OPTIONS (tabletype 'list', singleton_key 'myhugelist');
This looks like a useful possible enhancement.
- Improvements to replication
- Parallel query
- Index-organized tables
- Better partitioning
- Better full-text search
- Logical streaming replication
What we need to focus on is the reasons why people don't use PostgreSQL at all. Only by exploiting new markets -- by pushing Postgres into places which never had a database before -- do we grow the future PostgreSQL community. And there's a bunch of ways we are failing new users.
For example, listen to Nelson Elhage, engineer at Stripe.com:
"I love Mongo's HA story. Out of the box I can build a 3-node Mongo cluster with a full replica set. I can add nodes, I can fail over, without losing data."Wouldn't it be nice if we could say the same thing about Postgres? But we can't.
If we're looking for a #1 PostgreSQL development priority, this is it:
We need a "scale it now" button.
This is where we're losing ground to the new databases. In every other way we are superior: durability, pluggability, standards-compliance, query sophistication, everything. But when a PostgreSQL user outstrips the throughput of a single server or a single EC2 instance, our process for scaling out sucks. It's complicated. It has weird limitations. And most of all, our scale-out requires advanced database expertise, which is expensive and in short supply.
We need some way for users to go smoothly and easily from one Postgres node to three and then to ten. Until we do that, we will continue to lose ground to databases which do a better job at scaling, even if they suck at everything else.
I don't know what our answer to scaling out will be. Streaming logical replication, PostgresXC, Tr
Sorry for any inconvenience.
As per Unicode documentation, the presence of BOM in file are useless, because it causes problems with non-BOM-aware software's to identify or parse the leading characters having at the start. Same has been quoted at the bottom of the Wikipedia page:
Related errors in PostgreSQL:
ERROR: invalid input syntax for integer: "9" (in psql-client)
SQL state: 22P02 (in PgAdmin-III)
Test case & fix on Windows:
Sample file "state_data.txt" created in NOTEPAD with unicode characters in it:
9,Karnataka,कर्नाटकTable to import data:
10,Kerala,केरळा
create table states(state_code int, state_name char(30), state_in_hindi text);Error:
postgres=# copy test from 'c:/Pgfile/state_data.txt' with delimiter ',' CSV;To fix, I have used a tool "bomremover.exe" to remove leading characters from a file as its on windows, if its on linux, then there are many tips & tricks available on net to wipe BOM from a utf-8 format file.
ERROR: invalid input syntax for integer: "9"
CONTEXT: COPY test, line 1, column state_code: "9"
Tool Download link and usage:
http://www.mannaz.at/codebase/utf-byte-order-mark-bom-remover/
Eg:-After running bomremover.exe on file, re-run COPY command which will succeed to import data.
C:\Pgfile>bomremover.exe . *
Added '.\state_data.txt' to processing list.
Press enter to process all files in the list. (1 files in total)
Processing file '.\state_data.txt'...
Finished. Press Enter to Exit
state_code | state_name | State_name_in_hindi
------------+------------+---------------------
9 | Karnataka | αñòαñ░αÑ<8d>αñ¿αñ╛αñƒαñò
10 | Kerala | केरळा
(2 rows)
Some of the editors, avoids default saving text with UTF8-BOM:
- Windows - Notepad++ (In Notepade default BOM enabled)
- Linux - VI
- Mac - TextEdit
--Raghav
Since the launch they have been hit by a huge flood of users and last night things started to drag, and they asked PostgreSQL Experts for some emergency help. The app is backed by a PostgreSQL database running on Heroku. During a phone call of two hours, I was able to get in, examine the database, and devise some steps that we hope will have the effect of making things run smoother.
The solution is going to involve some partitioning of one or two tables. This isn't being done so much for the benefit of constraint exclusion as because it lets them drop large amounts of data that is no longer needed, cheaply. Previously they had been cleaning one of these tables by deleting rows, but abandoned that as it adversely affected performance. But the consequence of that was that the table just grew and grew to several million rows in a few days. Dropping child tables that contain data that is of no further interest will work well to tame the data size without all the overhead of row deletion.
Troubles sometimes don't come singly. I actually had to deal with two emergencies last night, which is quite unusual - the other was also for a client running on Amazon. For good or ill, we are seeing more and more use of Postgres instances running in the cloud - whether managed by the client or on a managed service such as Heroku.
There are many new changes introduced in PostgreSQL 9.3 beta1 and I do have few favorites in them.
For example Disk page checksums to detect filesystem failures. In fact this would allow VMware to use the now standard disk page checksum instead of a custom feature. This highly debated feature is required to identify silent bit corruptions (or deter malicious ones). I have been told in talks with database administrators (not just PostgreSQL DBAs) that typically in a year they would face one such incident atleast where one of the disk would show such a bit rot which goes unnoticed without any instrumentations to catch it.
Another change that goes in the right direction is how PostgreSQL maps the shared memory. This small change now allows no kernel changes to be done to start the database with a bigger shared buffer pool. This now allows one less cookbook step to be done to get the database working. Considering that in this cloud world where there are 100,000 VMs running databases one less step is a huge increase in productivity since this step actually required privileges higher than the database instance owner.
Yet another favorite feature is the custom background workers. This new mechanism is certainly a popular one in our team at VMware where are using it heavily to move some of the changes that we had done into custom background workers deployed as extensions and allowed us to align with core PostgreSQL and extra features enabled as extensions as needed.
Next I want to talk of three features : Writeable Foreign Tables and pgsql Foreign Data Wrapper and Automatically update VIEWs together. These features on its own itself are very useful and generic. However when used together it actually opens new possibilities using multiple federated PostgreSQL databases shards w
A week after the release of PgLife, the site is averaging thirty active users. (I define an active user as an IP address that has viewed the site for at least five minutes during the past hour.) I consider that a success. Since the release of PgLife, I have increased the content update interval and added an About page explaining the site's purpose, which also includes the active user count.
The site uses AJAX, Perl, Procmail rules, and Apache to collect and deliver dynamic content. Recent improvements in the Postgres mailing list archive feature set have made linking to emails much simpler.
PgLife was my first attempt at a dynamic website, and I learned a few things. First, I learned the value of having an alert file that can force a browser reload to push fixes and improvements to the browser. Second, I used the same file to allow pushing of news alerts to users, e.g. 9.3 Beta1. Third, I learned the importance of controlling browser and server caching and revalidation when using dynamic content.