pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhich version of PostgreSQL should I use.
8+ messages / 5 participants
[nested] [flat]
* Which version of PostgreSQL should I use.
@ 2011-05-16 23:04 Gavin Baumanis <gavinb@thespidernet.com>
2011-05-21 23:10 ` Re: Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
2011-05-21 23:45 ` Re: Which version of PostgreSQL should I use. Basil Bourque <basil.list@me.com>
0 siblings, 2 replies; 8+ messages in thread
From: Gavin Baumanis @ 2011-05-16 23:04 UTC (permalink / raw)
To: pgsql-sql
Hi there,
Let me first preface this with, I am not a PostgreSQL admin.
I am a web developer who happens to use PSQL as the back-end for my company's app.
We did have a dedicated DBA / system admin - but he has recently resigned.
I know enough about psql - to be able to create / drop databases... and enough about SQL to get stuff in and out of the database.
Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail.
We're currently using psql 8.1 and are on the way to upgrading to 8.4.
This is a process that the last DBA had us start.
And we've slowly been going through our code, getting rid of implicit casts as errors appear.
I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement.
And subsequently have some questions, please.
Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases.
My initial thought is;
We should upgrade to the latest stable version - whatever that is;
But is the answer that simple?
What we do we also need to take into account?
I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that we had to upgrade to 8.3 first.
So I guess I am hoping that someone might just simply know - or be able to pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1
AS always - thanks in advance for any assistance you might be able to give us!
Gavin.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
2011-05-16 23:04 Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
@ 2011-05-21 23:10 ` Gavin Baumanis <gavinb@thespidernet.com>
2011-05-23 15:19 ` Re: Which version of PostgreSQL should I use. jasmin.dizdarevic@gmail.com
1 sibling, 1 reply; 8+ messages in thread
From: Gavin Baumanis @ 2011-05-21 23:10 UTC (permalink / raw)
To: Gavin Baumanis <gavinb@thespidernet.com>; +Cc: pgsql-sql
Just thought I would update this myself.
Last night I managed to have a chat with some people about this on IRC.
Turns out we're in pretty good shape.
We already know about the changes to implicit casting and we have always used UTF-8 for encoding, too.
So the changes from 8.4 don't seem to be of a worry for us.
(I am sure that we will find SQL hidden in our app somewhere that will need to be modified for implicit casting...and we'll just fix those as we find them.)
Otherwise it seems like a simple case of using the 9.04 pg_dump / (pg_dumpall) application and restoring.
I also read chapters 15 / 24 of the excellent documentation about running both 8.1 and 9.04 at the same time and migrating a database at a time.
So, I suppose unless there is something obvious, (to someone else) - we have all the information we need.
Gavin "Beau" Baumanis
On 17/05/2011, at 9:04 AM, Gavin Baumanis wrote:
> Hi there,
>
> Let me first preface this with, I am not a PostgreSQL admin.
> I am a web developer who happens to use PSQL as the back-end for my company's app.
> We did have a dedicated DBA / system admin - but he has recently resigned.
> I know enough about psql - to be able to create / drop databases... and enough about SQL to get stuff in and out of the database.
> Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail.
>
>
> We're currently using psql 8.1 and are on the way to upgrading to 8.4.
> This is a process that the last DBA had us start.
> And we've slowly been going through our code, getting rid of implicit casts as errors appear.
>
> I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement.
> And subsequently have some questions, please.
>
> Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases.
> My initial thought is;
> We should upgrade to the latest stable version - whatever that is;
> But is the answer that simple?
>
> What we do we also need to take into account?
> I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that we had to upgrade to 8.3 first.
>
> So I guess I am hoping that someone might just simply know - or be able to pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1
>
> AS always - thanks in advance for any assistance you might be able to give us!
>
> Gavin.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
2011-05-16 23:04 Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
2011-05-21 23:10 ` Re: Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
@ 2011-05-23 15:19 ` jasmin.dizdarevic@gmail.com
2011-05-23 23:18 ` Re: Which version of PostgreSQL should I use. Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 8+ messages in thread
From: jasmin.dizdarevic@gmail.com @ 2011-05-23 15:19 UTC (permalink / raw)
To: Gavin Baumanis <gavinb@thespidernet.com>; pgsql-sql-owner@postgresql.org; +Cc: pgsql-sql
Just be careful with pg_dump, if you have binary data stored in your 8.4 db. In default mode it just export text.
Jasmin
A1 präsentiert BlackBerry® von Vodafone
-----Original Message-----
From: Gavin Baumanis <gavinb@thespidernet.com>
Sender: pgsql-sql-owner@postgresql.orgDate: Sun, 22 May 2011 09:10:17
To: Gavin Baumanis<gavinb@thespidernet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] Which version of PostgreSQL should I use.
Just thought I would update this myself.
Last night I managed to have a chat with some people about this on IRC.
Turns out we're in pretty good shape.
We already know about the changes to implicit casting and we have always used UTF-8 for encoding, too.
So the changes from 8.4 don't seem to be of a worry for us.
(I am sure that we will find SQL hidden in our app somewhere that will need to be modified for implicit casting...and we'll just fix those as we find them.)
Otherwise it seems like a simple case of using the 9.04 pg_dump / (pg_dumpall) application and restoring.
I also read chapters 15 / 24 of the excellent documentation about running both 8.1 and 9.04 at the same time and migrating a database at a time.
So, I suppose unless there is something obvious, (to someone else) - we have all the information we need.
Gavin "Beau" Baumanis
On 17/05/2011, at 9:04 AM, Gavin Baumanis wrote:
> Hi there,
>
> Let me first preface this with, I am not a PostgreSQL admin.
> I am a web developer who happens to use PSQL as the back-end for my company's app.
> We did have a dedicated DBA / system admin - but he has recently resigned.
> I know enough about psql - to be able to create / drop databases... and enough about SQL to get stuff in and out of the database.
> Beyond that - I pretty much know , nothing about PostgreSQL - thus this mail.
>
>
> We're currently using psql 8.1 and are on the way to upgrading to 8.4.
> This is a process that the last DBA had us start.
> And we've slowly been going through our code, getting rid of implicit casts as errors appear.
>
> I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement.
> And subsequently have some questions, please.
>
> Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases.
> My initial thought is;
> We should upgrade to the latest stable version - whatever that is;
> But is the answer that simple?
>
> What we do we also need to take into account?
> I am "pretty sure" that to get to 8.4 from 8.1 (on our staging server) that we had to upgrade to 8.3 first.
>
> So I guess I am hoping that someone might just simply know - or be able to pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1
>
> AS always - thanks in advance for any assistance you might be able to give us!
>
> Gavin.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
2011-05-16 23:04 Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
2011-05-21 23:10 ` Re: Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
2011-05-23 15:19 ` Re: Which version of PostgreSQL should I use. jasmin.dizdarevic@gmail.com
@ 2011-05-23 23:18 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 8+ messages in thread
From: Craig Ringer @ 2011-05-23 23:18 UTC (permalink / raw)
To: jasmin.dizdarevic@gmail.com; +Cc: Gavin Baumanis <gavinb@thespidernet.com>; pgsql-sql-owner@postgresql.org; pgsql-sql
On 23/05/2011 11:19 PM, jasmin.dizdarevic@gmail.com wrote:
> Just be careful with pg_dump, if you have binary data stored in your 8.4 db. In default mode it just export text.
Er ... what?
Can you elaborate on that?
Do you mean large objects? bytea fields? Something else?
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
2011-05-16 23:04 Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
@ 2011-05-21 23:45 ` Basil Bourque <basil.list@me.com>
1 sibling, 0 replies; 8+ messages in thread
From: Basil Bourque @ 2011-05-21 23:45 UTC (permalink / raw)
To: pgsql-sql
> We're currently using psql 8.1 and are on the way to upgrading to 8.4.
The tools "pg_dump" & "pg_restore" are used to extract and re-create databases. Can be used for chores such as deploying from development to production, backing up, and upgrading.
http://www.postgresql.org/docs/current/static/app-pgdump.html
http://www.postgresql.org/docs/9.0/static/app-pgrestore.html
The tool "pg_upgrade" is intended to help you upgrade from one version to another. Think of it as a wrapper around pg_dump & pg_restore. Has a bonus feature where you can upgrade a database in place rather than re-create it if you have a huge amount of data and too little time at the moment of officially upgrading to afford re-creating the data.
http://www.postgresql.org/docs/current/static/pgupgrade.html
You may want to go to version 9 rather than 8.4.
a) 9.0 is not a major upgrade in terms of compatibility. Original plans labeled it as 8.5 for that reason. "9" was eventually chosen as a label only because of dramatic new features added.
b) Some hooks were added to 9 to allow enhancements to the 'pg_upgrade' tool. It may be actually be easier to upgrade to 9 than 8.4 for that reason.
> I have now been asked to start replicating our databases between servers - as a hot-copy / redundancy improvement.
A relatively simple and built-in replication system was one of the major features added to version 9.0.
http://www.postgresql.org/docs/current/static/high-availability.html
> Is there are a particular version of PostgreSQL that we should be "aiming" to upgrade to that provides for synching of databases.
> My initial thought is;
> We should upgrade to the latest stable version - whatever that is;
Version 9.0.4 is the current stable version. Version 9.1 is due out soon.
> pint me in the correct direction for some information about what's in what version and any upgrade requirements to get to XXX from 8.1
http://www.postgresql.org/docs/current/static/release.html
--Basil Bourque
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
@ 2011-05-24 06:48 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:58 ` Re: Which version of PostgreSQL should I use. Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Craig Ringer @ 2011-05-24 06:48 UTC (permalink / raw)
To: jasmin.dizdarevic@gmail.com; pgsql-sql
On 24/05/11 14:30, jasmin.dizdarevic@gmail.com wrote:
> We had trbls with our drupal site, because some settings are stored in bytea columns. I think lobs are a problem too, but pls see the pg_dump docs for details.
Is it possible that you are referring to the `bytea_output' setting in
PostgreSQL 8.4 and above?
If so, the issue isn't that pg_dump somehow fails to dump the bytea
data. Rather, it's that drupal doesn't deal well with bytea data from
newer versions of PostgreSQL until the bytea_output setting is changed
to 'escape' because it doesn't understand the new hex format.
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
2011-05-24 06:48 Re: Which version of PostgreSQL should I use. Craig Ringer <craig@postnewspapers.com.au>
@ 2011-05-24 10:58 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-25 01:56 ` Re: Which version of PostgreSQL should I use. Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 8+ messages in thread
From: Jasmin Dizdarevic @ 2011-05-24 10:58 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; pgsql-sql
Alright, I've misunderstood this issue. Do you have to escape bytea columns
during export or import? And how you would do this?
Ty
2011/5/24 Craig Ringer <craig@postnewspapers.com.au>
> On 24/05/11 14:30, jasmin.dizdarevic@gmail.com wrote:
> > We had trbls with our drupal site, because some settings are stored in
> bytea columns. I think lobs are a problem too, but pls see the pg_dump docs
> for details.
>
> Is it possible that you are referring to the `bytea_output' setting in
> PostgreSQL 8.4 and above?
>
> If so, the issue isn't that pg_dump somehow fails to dump the bytea
> data. Rather, it's that drupal doesn't deal well with bytea data from
> newer versions of PostgreSQL until the bytea_output setting is changed
> to 'escape' because it doesn't understand the new hex format.
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Which version of PostgreSQL should I use.
2011-05-24 06:48 Re: Which version of PostgreSQL should I use. Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:58 ` Re: Which version of PostgreSQL should I use. Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-05-25 01:56 ` Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 8+ messages in thread
From: Craig Ringer @ 2011-05-25 01:56 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
On 24/05/11 18:58, Jasmin Dizdarevic wrote:
> Alright, I've misunderstood this issue. Do you have to escape bytea
> columns during export or import? And how you would do this?
Some database drivers and some apps don't understand the new hex output
format for bytea columns.
IIRC, the format change should ONLY affect how bytea values are sent
from the server to the client using the standard text-based postgresql
protocol.
When using apps/drivers that aren't ready for the hex format yet, you must
SET bytea_output TO 'escape';
This can be done at the postgresql.conf level (globally), by ALTERing
the database the app uses, by ALTERing the user ID the app connects
with, or by modifying the app so it knows to issue an explicit SET
before doing any work with a connection.
AFAIK there is no impact on dump/load, though you *could* see problems
if you used an application's own dump/load feature rather than pg_dump
and the app wasn't ready for the new bytea format.
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-05-25 01:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-16 23:04 Which version of PostgreSQL should I use. Gavin Baumanis <gavinb@thespidernet.com>
2011-05-21 23:10 ` Gavin Baumanis <gavinb@thespidernet.com>
2011-05-23 15:19 ` jasmin.dizdarevic@gmail.com
2011-05-23 23:18 ` Craig Ringer <craig@postnewspapers.com.au>
2011-05-21 23:45 ` Basil Bourque <basil.list@me.com>
2011-05-24 06:48 Re: Which version of PostgreSQL should I use. Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:58 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-25 01:56 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox