(As usual) I didn't explain myself very clearly so taking a step back I tried a different approach:
1. Step1
postgres=# update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname =
'template0';
2. Following
http://www.postgresql.org/docs/9.4/static/app-pgrestore.html
$createdb -T template0 ***db
which gives
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
***db | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | LATIN1 | en_GB | en_GB |
template0 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | LATIN1 | en_GB | en_GB | postgres=CTc/postgres+
| | | | | =c/postgres
test | postgres | LATIN1 | en_GB | en_GB |
(6 rows)
3.
$pg_restore -d ***db_database_dump201507131900
And the tables and data now seem to be back! I must have got confused before as tom lane suggested
Thanks for your help
Austen
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 July 2015 01:36
To: Birchall, Austen
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] pg_restore hangs for en_US.utf8 in the UK
"Birchall, Austen" <austen.birchall@metoffice.gov.uk> writes:
> Trying to restore the data after restoring the tables etc. from a
> pg_dumpall out.sql type file using pg_restore -d
> ***db_database_dump201507131900 -v just hangs
pg_dumpall produces a SQL script; you should feed that to psql not pg_restore.
I'm not sure though why you're seeing it hang; all the versions I have handy produce some kind of error message when
feda script file.
Hmm ... is that the *whole* command line? If so problem is that it's patiently waiting for some input on stdin.
regards, tom lane
