[GENERAL] Re: Large values for duration of COMMITs and slow queries. Due to large WAL config values?

[Cody Caughlan]

I've run VACUUM ANALYZE on all my tables to make sure the house has
been cleaned. I still see a lot of slow queries / commits, even on
primary key lookups and well indexed tables.

/Cody

On Fri, Nov 11, 2011 at 11:04 PM, Cody Caughlan tool...@gmail.com wrote:
 Postgres 9.1.1, master with 2 slaves via streaming replication.

 I've enabled slow query logging of 150ms and am seeing a large number
 of slow COMMITs:

 2011-11-12 06:55:02 UTC pid:30897 (28/0-0) LOG:  duration: 232.398 ms
 statement: COMMIT
 2011-11-12 06:55:08 UTC pid:30896 (27/0-0) LOG:  duration: 1078.789 ms
  statement: COMMIT
 2011-11-12 06:55:09 UTC pid:30842 (15/0-0) LOG:  duration: 2395.432 ms
  statement: COMMIT
 2011-11-12 06:55:09 UTC pid:30865 (23/0-0) LOG:  duration: 2395.153 ms
  statement: COMMIT
 2011-11-12 06:55:09 UTC pid:30873 (17/0-0) LOG:  duration: 2390.106 ms
  statement: COMMIT

 The machine has 16GB of RAM and plenty of disk space. What I think
 might be relevant settings are:

 wal_buffers = 16MB
 checkpoint_segments = 32
 max_wal_senders = 10
 checkpoint_completion_target = 0.9
 wal_keep_segments = 1024
 maintenance_work_mem = 256MB
 work_mem = 88MB
 shared_buffers = 3584MB
 effective_cache_size = 10GB

 Recently we have bumped up wal_keep_segments and checkpoint_segments
 because we wanted to run long running queries on the slaves and we're
 receiving cancellation errors on the slaves. I think the master was
 recycling WAL logs from underneath the slave and thus canceling the
 queries. Hence, I believed I needed to crank up those values. It seems
 to work, I can run long queries (for statistics / reports) on the
 slaves just fine.

 But I now wonder if its having an adverse effect on the master, ala
 these slow commit times and other slow queries (e.g. primary key
 lookups on tables with not that many records), which seem to have
 increased since the configuration change.

 I am watching iostat and sure enough, when %iowait gets  15 or so
 then a bunch more slow queries get logged. So I can see its disk
 related.

 I just dont know what the underlying cause is.

 Any pointers would be appreciated. Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Large values for duration of COMMITs and slow queries. Due to large WAL config values?

[Cody Caughlan]

Postgres 9.1.1, master with 2 slaves via streaming replication.

I've enabled slow query logging of 150ms and am seeing a large number
of slow COMMITs:

2011-11-12 06:55:02 UTC pid:30897 (28/0-0) LOG:  duration: 232.398 ms
statement: COMMIT
2011-11-12 06:55:08 UTC pid:30896 (27/0-0) LOG:  duration: 1078.789 ms
 statement: COMMIT
2011-11-12 06:55:09 UTC pid:30842 (15/0-0) LOG:  duration: 2395.432 ms
 statement: COMMIT
2011-11-12 06:55:09 UTC pid:30865 (23/0-0) LOG:  duration: 2395.153 ms
 statement: COMMIT
2011-11-12 06:55:09 UTC pid:30873 (17/0-0) LOG:  duration: 2390.106 ms
 statement: COMMIT

The machine has 16GB of RAM and plenty of disk space. What I think
might be relevant settings are:

wal_buffers = 16MB
checkpoint_segments = 32
max_wal_senders = 10
checkpoint_completion_target = 0.9
wal_keep_segments = 1024
maintenance_work_mem = 256MB
work_mem = 88MB
shared_buffers = 3584MB
effective_cache_size = 10GB

Recently we have bumped up wal_keep_segments and checkpoint_segments
because we wanted to run long running queries on the slaves and we're
receiving cancellation errors on the slaves. I think the master was
recycling WAL logs from underneath the slave and thus canceling the
queries. Hence, I believed I needed to crank up those values. It seems
to work, I can run long queries (for statistics / reports) on the
slaves just fine.

But I now wonder if its having an adverse effect on the master, ala
these slow commit times and other slow queries (e.g. primary key
lookups on tables with not that many records), which seem to have
increased since the configuration change.

I am watching iostat and sure enough, when %iowait gets  15 or so
then a bunch more slow queries get logged. So I can see its disk
related.

I just dont know what the underlying cause is.

Any pointers would be appreciated. Thank you.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Insufficient privilege when initiating backup

[Cody Caughlan]

I am attempting to run

select pg_start_backup('backup-2011-11-09');

But it is just hanging, I've given it 3 minutes. When I view
pg_stat_activity I see insufficient privilege in the current_query
column.

I've quadruple checked that the user (postgres) has the Replication role:

batch_api_production= \du+
 List of roles
 Role name  |   Attributes   | Member of |
Description
++---+-
 batch  | Create DB  | {}|
 postgres   | Superuser, Create role, Create DB, Replication | {}|
 replicator | Replication   +| {}|
| 2 connections  |   |


Any help would be appreciated.

/Cody

Re: [GENERAL] pg_restore: [custom archiver] unexpected end of file on Postgres 9.1.1

[Cody Caughlan]

Ok, I think I've narrowed down the problem. Doing a pg_dump with --verbose
and watching it myself (it was in a cron before), I now see:

pg_dump: dumping contents of table external_users
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  canceling statement due to
conflict with recovery
DETAIL:  User query might have needed to see row versions that must be
removed.
pg_dump: The command was: COPY public.external_users (id, user_id,
external_id, type) TO stdout;
pg_dump: *** aborted because of error

The pg_dump is being run from a slave set on hot-standby mode. By looking
around this appears to be a fairly common issue with streaming replication.

I have found references to this manual page:

http://www.postgresql.org/docs/9.0/static/hot-standby.html

In my case external_users is a pretty hot table, so I think it
satisfies this note: Users should be clear that tables that are regularly
and heavily updated on the primary server will quickly cause cancellation
of longer running queries on the standby

In my case I have:

max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s

I dont know if adjusting one of the above parameters would help. From the
docs it sounds that increasing vacuum_defer_cleanup_age to some larger
value might also do the trick.

Any guidance would be appreciated.

/Cody

On Mon, Nov 7, 2011 at 4:11 PM, Adrian Klaver adrian.kla...@gmail.comwrote:

 On Monday, November 07, 2011 11:27:05 am Cody Caughlan wrote:
  I am trying to restore a dump created with pg_dump, both source and
  destination are Postgres 9.1.1 albeit different machines (source is
 Linux,
  destination is OS X).
 
  $ pg_restore -U postgres -Fc -d batch_api_production
  200708_batch_api_production.dump.sql
  pg_restore: [custom archiver] unexpected end of file
 
  pg_restore does seem like its doing something, as it doesnt error out
 until
  10-12 seconds in.
 
  I can dump the TOC just fine with pg_restore -l, which I've collected
 here:
 
  https://gist.github.com/951e417e7098fdf987d4
 
  If I access the DB it appears that all the tables and sequences exist,
 but
  none of the data or indexes  constraints.
 
  Any help would be appreciated.

 What do the database logs show when you do the restore?

 
  /Cody Caughlan

 --
 Adrian Klaver
 adrian.kla...@gmail.com

[GENERAL] pg_restore: [custom archiver] unexpected end of file on Postgres 9.1.1

[Cody Caughlan]

I am trying to restore a dump created with pg_dump, both source and
destination are Postgres 9.1.1 albeit different machines (source is Linux,
destination is OS X).

$ pg_restore -U postgres -Fc -d batch_api_production
200708_batch_api_production.dump.sql
pg_restore: [custom archiver] unexpected end of file

pg_restore does seem like its doing something, as it doesnt error out until
10-12 seconds in.

I can dump the TOC just fine with pg_restore -l, which I've collected here:

https://gist.github.com/951e417e7098fdf987d4

If I access the DB it appears that all the tables and sequences exist, but
none of the data or indexes  constraints.

Any help would be appreciated.

/Cody Caughlan

Re: [GENERAL] Searching for bare letters

[Cody Caughlan]

One approach would be to normalize all the text and search against that.

That is, basically convert all non-ASCII characters to their equivalents. 

I've had to do this in Solr for searching for the exact reasons you've 
outlined: treat ñ as n. Ditto for ü - u, é = e, etc.

This is easily done in Solr via the included ASCIIFoldingFilterFactory:

http://wiki.apache.org/solr/AnalyzersTokenizersTokenFilters#solr.ASCIIFoldingFilterFactory

You could look at the code to see how they do the conversion and implement it.

/Cody

On Oct 1, 2011, at 7:09 PM, planas wrote:

 On Sun, 2011-10-02 at 01:25 +0200, Reuven M. Lerner wrote:
 Hi, everyone.  I'm working on a project on PostgreSQL 9.0 (soon to be 
 upgraded to 9.1, given that we haven't yet launched).  The project will 
 involve numerous text fields containing English, Spanish, and Portuguese.  
 Some of those text fields will be searchable by the user.  That's easy 
 enough to do; for our purposes, I was planning to use some combination of 
 LIKE searches; the database is small enough that this doesn't take very much 
 time, and we don't expect the number of searchable records (or columns 
 within those records) to be all that large.
 
 The thing is, the people running the site want searches to work on what I'm 
 calling (for lack of a better term) bare letters.  That is, if the user 
 searches for n, then the search should also match Spanish words containing 
 ñ.  I'm told by Spanish-speaking members of the team that this is how they 
 would expect searches to work.  However, when I just did a quick test using 
 a UTF-8 encoded 9.0 database, I found that PostgreSQL didn't  see the two 
 characters as identical.  (I must say, this is the behavior that I would 
 have expected, had the Spanish-speaking team member not said anything on the 
 subject.)
 
 So my question is whether I can somehow wrangle PostgreSQL into thinking 
 that n and ñ are the same character for search purposes, or if I need to 
 do something else -- use regexps, keep a naked, searchable version of each 
 column alongside the native one, or something else entirely -- to get this 
 to work.
 
 Could you parse the search string for the non-English characters and convert 
 them to the appropriate English character? My skills are not that good or I 
 would offer more details.
 Any ideas?
 
 Thanks,
 
 Reuven
 
 
 -- 
 Reuven M. Lerner -- Web development, consulting, and training
 Mobile: +972-54-496-8405 * US phone: 847-230-9795
 Skype/AIM: reuvenlerner
 
 
 -- 
 Jay Lozier
 jsloz...@gmail.com

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

Thanks y'all for your help on this.

I took this opportunity to upgrade to 9.1.1 which is UTF8 by default and I
ended up manually cleaning up the borked data by hand (there wasn't that
much).

So all is well now.

Thanks again.

/Cody



On Fri, Sep 30, 2011 at 3:37 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 2:57 PM, Cody Caughlan tool...@gmail.com wrote:
  Please see below.
 
  On Fri, Sep 30, 2011 at 1:12 PM, Scott Marlowe scott.marl...@gmail.com
  wrote:
 
  On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan tool...@gmail.com
 wrote:
   That worked, but file shows no difference:
   $ iconv -f utf-8 -t utf-8 -c foo.sql  utf.sql
   $ file -i foo.sql
   foo.sql: text/plain; charset=us-ascii
   $file -i utf.sql
   utf.sql: text/plain; charset=us-ascii
   So iconv didnt actually convert the file OR does is the file command
   just
   ignorant?
 
  Not sure.  try loading the dump into the UTF-8 DB in postgres and see
  what happens I guess?
 
 
  Uh oh.
  On the remote machine:
  $ pg_dump -Fc -E UTF8 foo  foo.sql
  Then I've created a new local DB with UTF8 encoding and I try to restore
  this dump into it:
  pg_restore: [archiver (db)] Error while PROCESSING TOC:
  pg_restore: [archiver (db)] Error from TOC entry 2342; 0 17086 TABLE DATA
  wine_books vinosmith
  pg_restore: [archiver (db)] COPY failed for table wine_books: ERROR:
   invalid byte sequence for encoding UTF8: 0xc309
  CONTEXT:  COPY wine_books, line 1147
  WARNING: errors ignored on restore: 1
  And sure enough the table wine_books is empty. Not good.

 You may have to hunt down that one bad line (1147) and chop it out /
 edit it so it works.

[GENERAL] Change server encoding after the fact

[Cody Caughlan]

I would like to change my server_encoding which is currently SQL_ASCII to UTF8.

I have existing data that I would like to keep.

From my understanding of the steps I need to:

1) alter the template1 database encoding via

UPDATE pg_database SET encoding = 6 where datname IN ('template0', 'template1');

2) Dump my current database

pg_dump -Fc foo  foo.db

3) Drop my current database

drop database foo;

4) recreate it with the proper encoding

create database foo with template = template1 encoding = 'UTF-8';

5) restore from backup

pg_restore -d foo foo.db



Are these the correct steps to perform or is there an easier / in-place way? 

Also, when I dump my old DB and restore it, will it be converted appropriately 
(e.g. it came from am SQL_ASCII encoding and its going into a UTF-8 database)?

Thank you

/Cody
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

Thanks Scott. See below:

On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan tool...@gmail.com wrote:
  I would like to change my server_encoding which is currently SQL_ASCII to
 UTF8.
 
  I have existing data that I would like to keep.
 
  From my understanding of the steps I need to:
 
  1) alter the template1 database encoding via
 
  UPDATE pg_database SET encoding = 6 where datname IN ('template0',
 'template1');

 Just create database using template0 as template and you can skip this step
 ^^



Wouldn't this only work if my template0 was UTF8 itself?

= select datname, pg_encoding_to_char(encoding) from pg_database;
   datname| pg_encoding_to_char
--+-
 template1| SQL_ASCII
 template0| SQL_ASCII
 postgres | SQL_ASCII


So it appears both template0  template1 are SQL_ASCII, so how would
creating from a new DB from template0 be any different than template1?



  Are these the correct steps to perform or is there an easier / in-place
 way?

  Also, when I dump my old DB and restore it, will it be converted
 appropriately (e.g. it came from am SQL_ASCII encoding and its going into a
 UTF-8 database)?

 You might need to set client encoding when restoring.  Or use iconv to
 convert from one encoding to another, which is what I usually do.
 Note that it's VERY likely you'll have data in a SQL_ASCII db that
 won't go into a UTF8 database without some lossiness.



Yes, I see this might be the case. From my playing around with iconv I
cannot even properly do the conversion:

$ pg_dump -Fp foo  foo.sql
$ file -i foo.sql
foo.sql: text/plain; charset=us-ascii
$ iconv -f utf-8 foo.sql  utf8.sql
iconv: illegal input sequence at position 2512661

Uh oh... I cannot event convert it?

Whats my next step at this point if I cannot even convert my data? I'd be OK
with some lossiness.

Thanks again
/Cody

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

Please see below.

On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan tool...@gmail.com wrote:
  Thanks Scott. See below:
 
  On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe scott.marl...@gmail.com
 
  wrote:
 
  On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan tool...@gmail.com
 wrote:
   I would like to change my server_encoding which is currently SQL_ASCII
   to UTF8.
  
   I have existing data that I would like to keep.
  
   From my understanding of the steps I need to:
  
   1) alter the template1 database encoding via
  
   UPDATE pg_database SET encoding = 6 where datname IN ('template0',
   'template1');
 
  Just create database using template0 as template and you can skip this
  step ^^
 
 
  Wouldn't this only work if my template0 was UTF8 itself?
  = select datname, pg_encoding_to_char(encoding) from pg_database;
 datname| pg_encoding_to_char
  --+-
   template1| SQL_ASCII
   template0| SQL_ASCII
   postgres | SQL_ASCII
 
  So it appears both template0  template1 are SQL_ASCII, so how would
  creating from a new DB from template0 be any different than template1?

 Well, let's try, shall we?  From a freshly created cluster on my
 laptop, running 8.4:

 smarlowe=# select datname, pg_encoding_to_char(encoding) from pg_database;
   datname  | pg_encoding_to_char
 ---+-
  template1 | SQL_ASCII
  template0 | SQL_ASCII
  postgres  | SQL_ASCII
  smarlowe  | SQL_ASCII
 (4 rows)

 smarlowe=# create database j template template0 encoding 'UTF8';
 CREATE DATABASE

 Seems to work.

 P.s. I'm not sure why it works, I just know that it does. :)


Ok, I see what you mean. This would create a new DB with the proper
encoding. Which is fine, and probably what I will do. I guess I see an
ideal scenario being one where we permanently convert the template encoding
to UTF8 so going forward I dont have to worry about forgetting to adding the
encoding= 'UTF8' for every new DB I create.


   Are these the correct steps to perform or is there an easier /
 in-place
   way?
 
   Also, when I dump my old DB and restore it, will it be converted
   appropriately (e.g. it came from am SQL_ASCII encoding and its going
 into a
   UTF-8 database)?
 
  You might need to set client encoding when restoring.  Or use iconv to
  convert from one encoding to another, which is what I usually do.
  Note that it's VERY likely you'll have data in a SQL_ASCII db that
  won't go into a UTF8 database without some lossiness.
 
 
  Yes, I see this might be the case. From my playing around with iconv I
  cannot even properly do the conversion:
  $ pg_dump -Fp foo  foo.sql
  $ file -i foo.sql
  foo.sql: text/plain; charset=us-ascii
  $ iconv -f utf-8 foo.sql  utf8.sql
  iconv: illegal input sequence at position 2512661

 I think you got it backwards, the -f should be somthing other than
 utf-8 right?  That's what the -t should be right?  Try iconv without a
 -f switch and a -t of utf-8 and see what happens...


You're right, I had -f when I needed -t. I tried it again with the same
error:

$ iconv -t utf-8 foo.sql  utf.sql
iconv: illegal input sequence at position 2512661

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

On Fri, Sep 30, 2011 at 12:31 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan tool...@gmail.com wrote:
  Please see below.
 
  On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe scott.marl...@gmail.com
 
  wrote:
 
  On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan tool...@gmail.com
 wrote:
   Thanks Scott. See below:
  
   On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe
   scott.marl...@gmail.com
   wrote:
  
   On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan tool...@gmail.com
   wrote:
I would like to change my server_encoding which is currently
SQL_ASCII
to UTF8.
   
I have existing data that I would like to keep.
   
From my understanding of the steps I need to:
   
1) alter the template1 database encoding via
   
UPDATE pg_database SET encoding = 6 where datname IN ('template0',
'template1');
  
   Just create database using template0 as template and you can skip
 this
   step ^^
  
  
   Wouldn't this only work if my template0 was UTF8 itself?
   = select datname, pg_encoding_to_char(encoding) from pg_database;
  datname| pg_encoding_to_char
   --+-
template1| SQL_ASCII
template0| SQL_ASCII
postgres | SQL_ASCII
  
   So it appears both template0  template1 are SQL_ASCII, so how would
   creating from a new DB from template0 be any different than template1?
 
  Well, let's try, shall we?  From a freshly created cluster on my
  laptop, running 8.4:
 
  smarlowe=# select datname, pg_encoding_to_char(encoding) from
 pg_database;
   datname  | pg_encoding_to_char
  ---+-
   template1 | SQL_ASCII
   template0 | SQL_ASCII
   postgres  | SQL_ASCII
   smarlowe  | SQL_ASCII
  (4 rows)
 
  smarlowe=# create database j template template0 encoding 'UTF8';
  CREATE DATABASE
 
  Seems to work.
 
  P.s. I'm not sure why it works, I just know that it does. :)
 
 
  Ok, I see what you mean. This would create a new DB with the proper
  encoding. Which is fine, and probably what I will do. I guess I see an
  ideal scenario being one where we permanently convert the template
 encoding
  to UTF8 so going forward I dont have to worry about forgetting to adding
 the
  encoding= 'UTF8' for every new DB I create.

 Ah ok.  The way I fix that is this:

 update pg_database set datistemplate = false where datname='template1';
 drop database template1;
 create database template1 template template0 encoding 'UTF8';

 But your way would likely work too.

  I think you got it backwards, the -f should be somthing other than
  utf-8 right?  That's what the -t should be right?  Try iconv without a
  -f switch and a -t of utf-8 and see what happens...
 
  You're right, I had -f when I needed -t. I tried it again with the same
  error:
  $ iconv -t utf-8 foo.sql  utf.sql
  iconv: illegal input sequence at position 2512661

 Any idea waht the actual encoding of your source database is?
 SQL_ASCII is basically not really ascii, more like anything goes.



How would I find this? pg_database says my DB is SQL_ASCII.

show all says

client_encoding = SQL_ASCII
server_encoding = SQL_ASCII

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

Its a Rails app and I do have:

  encoding: utf8

Set in my DB configuration.

On Fri, Sep 30, 2011 at 12:38 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 1:35 PM, Cody Caughlan tool...@gmail.com wrote:
 
 
  On Fri, Sep 30, 2011 at 12:31 PM, Scott Marlowe scott.marl...@gmail.com
 
  wrote:
 
  On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan tool...@gmail.com
 wrote:
   Please see below.
  
   On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe
   scott.marl...@gmail.com
   wrote:
  
   On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan tool...@gmail.com
   wrote:
Thanks Scott. See below:
   
On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe
scott.marl...@gmail.com
wrote:
   
On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan 
 tool...@gmail.com
wrote:
 I would like to change my server_encoding which is currently
 SQL_ASCII
 to UTF8.

 I have existing data that I would like to keep.

 From my understanding of the steps I need to:

 1) alter the template1 database encoding via

 UPDATE pg_database SET encoding = 6 where datname IN
 ('template0',
 'template1');
   
Just create database using template0 as template and you can skip
this
step ^^
   
   
Wouldn't this only work if my template0 was UTF8 itself?
= select datname, pg_encoding_to_char(encoding) from pg_database;
   datname| pg_encoding_to_char
--+-
 template1| SQL_ASCII
 template0| SQL_ASCII
 postgres | SQL_ASCII
   
So it appears both template0  template1 are SQL_ASCII, so how
 would
creating from a new DB from template0 be any different than
template1?
  
   Well, let's try, shall we?  From a freshly created cluster on my
   laptop, running 8.4:
  
   smarlowe=# select datname, pg_encoding_to_char(encoding) from
   pg_database;
datname  | pg_encoding_to_char
   ---+-
template1 | SQL_ASCII
template0 | SQL_ASCII
postgres  | SQL_ASCII
smarlowe  | SQL_ASCII
   (4 rows)
  
   smarlowe=# create database j template template0 encoding 'UTF8';
   CREATE DATABASE
  
   Seems to work.
  
   P.s. I'm not sure why it works, I just know that it does. :)
  
  
   Ok, I see what you mean. This would create a new DB with the proper
   encoding. Which is fine, and probably what I will do. I guess I see
 an
   ideal scenario being one where we permanently convert the template
   encoding
   to UTF8 so going forward I dont have to worry about forgetting to
 adding
   the
   encoding= 'UTF8' for every new DB I create.
 
  Ah ok.  The way I fix that is this:
 
  update pg_database set datistemplate = false where datname='template1';
  drop database template1;
  create database template1 template template0 encoding 'UTF8';
 
  But your way would likely work too.
 
   I think you got it backwards, the -f should be somthing other than
   utf-8 right?  That's what the -t should be right?  Try iconv without
 a
   -f switch and a -t of utf-8 and see what happens...
  
   You're right, I had -f when I needed -t. I tried it again with the
 same
   error:
   $ iconv -t utf-8 foo.sql  utf.sql
   iconv: illegal input sequence at position 2512661
 
  Any idea waht the actual encoding of your source database is?
  SQL_ASCII is basically not really ascii, more like anything goes.
 
 
  How would I find this? pg_database says my DB is SQL_ASCII.
  show all says
  client_encoding = SQL_ASCII
  server_encoding = SQL_ASCII

 It would have been set by the application accessing postgresql and
 inserting the data.  I.e. was it a windows app using a typical windows
 encoding?  etc.

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

That worked, but file shows no difference:

$ iconv -f utf-8 -t utf-8 -c foo.sql  utf.sql
$ file -i foo.sql
foo.sql: text/plain; charset=us-ascii

$file -i utf.sql
utf.sql: text/plain; charset=us-ascii

So iconv didnt actually convert the file OR does is the file command just
ignorant?

On Fri, Sep 30, 2011 at 12:41 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 1:39 PM, Cody Caughlan tool...@gmail.com wrote:
  Its a Rails app and I do have:
encoding: utf8


 Hmmm, if you try this does it work (mostly)?

 iconv -f utf-8 -t utf-8 -c  infile  outfile

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

Please see below.

On Fri, Sep 30, 2011 at 1:12 PM, Scott Marlowe scott.marl...@gmail.comwrote:

 On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan tool...@gmail.com wrote:
  That worked, but file shows no difference:
  $ iconv -f utf-8 -t utf-8 -c foo.sql  utf.sql
  $ file -i foo.sql
  foo.sql: text/plain; charset=us-ascii
  $file -i utf.sql
  utf.sql: text/plain; charset=us-ascii
  So iconv didnt actually convert the file OR does is the file command
 just
  ignorant?

 Not sure.  try loading the dump into the UTF-8 DB in postgres and see
 what happens I guess?



Uh oh.

On the remote machine:

$ pg_dump -Fc -E UTF8 foo  foo.sql

Then I've created a new local DB with UTF8 encoding and I try to restore
this dump into it:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2342; 0 17086 TABLE DATA
wine_books vinosmith
pg_restore: [archiver (db)] COPY failed for table wine_books: ERROR:
 invalid byte sequence for encoding UTF8: 0xc309
CONTEXT:  COPY wine_books, line 1147
WARNING: errors ignored on restore: 1

And sure enough the table wine_books is empty. Not good.