Thread

1. Always include encoding of database in pg_dumpall

   Jeremy Evans <code@jeremyevans.net> — 2012-10-18T17:44:33Z

   I've have a PostgreSQL database cluster that I've continually upgraded
   from 7.1 to 9.1 without problems using pg_dumpall and psql.  When
   migrating to 9.2, I decided to change the default encoding for the
   database cluster from SQL_ASCII to UTF8.  When I went to restore my
   database backup (created using 9.1's pg_dumpall), the tables
   containing data not in UTF8 format were empty.
   
   This appears to be caused by pg_dumpall omitting the ENCODING when
   dumping if it is the same as the current database cluster's default
   encoding.  I believe this is a bad idea, as there is no guarantee the
   backup will be restored on a cluster with the same default encoding.
   
   This patch always sets the ENCODING for the databases, even if it is
   the same as the current database cluster default encoding. Thoughts?
   
   I'm not sure if similar changes should be made for LC_COLLATE or
   LC_CTYPE, but that's something that should be considered.
   
   Please CC me when responding as I don't currently subscribe to the
   list.
   
   Thanks,
   Jeremy
   

2. Re: Always include encoding of database in pg_dumpall

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-10-19T22:55:02Z

   Jeremy Evans <code@jeremyevans.net> writes:
   > I've have a PostgreSQL database cluster that I've continually upgraded
   > from 7.1 to 9.1 without problems using pg_dumpall and psql.  When
   > migrating to 9.2, I decided to change the default encoding for the
   > database cluster from SQL_ASCII to UTF8.  When I went to restore my
   > database backup (created using 9.1's pg_dumpall), the tables
   > containing data not in UTF8 format were empty.
   
   I assume there were some errors reported?
   
   > This appears to be caused by pg_dumpall omitting the ENCODING when
   > dumping if it is the same as the current database cluster's default
   > encoding.  I believe this is a bad idea, as there is no guarantee the
   > backup will be restored on a cluster with the same default encoding.
   
   I'm not persuaded by this argument.  The choice to omit default encoding
   and locale settings was intentional; if you choose to load into an
   installation with different defaults, it's probably because you *want*
   to change the encoding/locale you're using.  What you propose would make
   it impossible to do that.  Indeed, I'd have thought that was exactly
   the use-case you were trying to accomplish here.
   
   > I'm not sure if similar changes should be made for LC_COLLATE or
   > LC_CTYPE, but that's something that should be considered.
   
   It would be a particularly bad idea to change the behavior for the
   locale names, because those aren't consistent across platforms.
   If we didn't suppress them when possible, cross-platform restores
   would likely become impossible.
   
   			regards, tom lane
   
   
   

3. Re: Always include encoding of database in pg_dumpall

   Jeremy Evans <code@jeremyevans.net> — 2012-10-19T23:19:19Z

   On 10/19 06:55, Tom Lane wrote:
   > Jeremy Evans <code@jeremyevans.net> writes:
   > > I've have a PostgreSQL database cluster that I've continually upgraded
   > > from 7.1 to 9.1 without problems using pg_dumpall and psql.  When
   > > migrating to 9.2, I decided to change the default encoding for the
   > > database cluster from SQL_ASCII to UTF8.  When I went to restore my
   > > database backup (created using 9.1's pg_dumpall), the tables
   > > containing data not in UTF8 format were empty.
   > 
   > I assume there were some errors reported?
    
   Yes.  Of course, using psql to restore the dump, the error messages are
   interspersed with all of the notices output, making it harder to see the
   errors.
   
   > > This appears to be caused by pg_dumpall omitting the ENCODING when
   > > dumping if it is the same as the current database cluster's default
   > > encoding.  I believe this is a bad idea, as there is no guarantee the
   > > backup will be restored on a cluster with the same default encoding.
   > 
   > I'm not persuaded by this argument.  The choice to omit default encoding
   > and locale settings was intentional; if you choose to load into an
   > installation with different defaults, it's probably because you *want*
   > to change the encoding/locale you're using.  What you propose would make
   > it impossible to do that.  Indeed, I'd have thought that was exactly
   > the use-case you were trying to accomplish here.
    
   My use case was just to change the default encoding to UTF8 so I
   wouldn't have to set the encoding manually when creating databases in
   the future, I didn't want to change the encoding of any of my current
   databases.
   
   I think the current behavior gives preference to usability at the
   expense of reliability, but I understand that that might be an
   appropriate decision to make.
   
   > > I'm not sure if similar changes should be made for LC_COLLATE or
   > > LC_CTYPE, but that's something that should be considered.
   > 
   > It would be a particularly bad idea to change the behavior for the
   > locale names, because those aren't consistent across platforms.
   > If we didn't suppress them when possible, cross-platform restores
   > would likely become impossible.
   
   That makes sense.
   
   Thanks for considering my patch. :)
   
   Jeremy