Re: pg_dump on local Windows, pg_restore on Linux?

On Wednesday 15. August 2007, Phoenix Kiula wrote:
>Couple of questions with porting:
>
>1. I have been playing around with my databases locally on Win XP so
>as not to hurt our website traffic. Now I would like to move the
>database to a Linux CentOS server. Can I use pg_dump on Windows and
>pg_restore it on Linux? If so, any tips on what I should keep in mind
>(e.g., manual seems to suggest that pg_restore prefers tar gzipped
>format...but I'm not sure if Windows can generate this?)

This is how I import my output from pg_dump:

createdb --encoding=UNICODE $DB
psql -U postgres -d $DB -f $INFILE > restore.log 2>&1

This way, you just feed psql with an uncompressed dump file. I've never
used pg_restore.

>2. I would like my database to be UTF-8. I issue the command
>
>CREATE DATABASE mydb OWNER me ENCODING 'utf8';
>
>Should I add anything else to it, such as collation? I did not find
>any option for that in here:
>http://www.postgresql.org/docs/8.2/static/sql-createdatabase.html

At least on a *nix system, collation is based on the value of the LC_ALL
environment variable at dbinit time. There's nothing you can do about
it in a live database. IMO that's a little awkward, and is what finally
made me change the global from ISO-8859-1 to UTF-8 on my three Gentoo
Linux machines.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/