Thread: Which version of PostgreSQL should I use.
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 informationabout 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.
> 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 deployingfrom 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 ahuge 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 eventuallychosen 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 to9 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 ofdatabases. > 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 getto XXX from 8.1 http://www.postgresql.org/docs/current/static/release.html --Basil Bourque
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'lljust 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 migratinga 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 thedatabase. > 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 ofdatabases. > 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 informationabout 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
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'lljust 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 migratinga 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 thedatabase. > 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 ofdatabases. > 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 informationabout 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
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/
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, butpls 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
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
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