Postgresql – Restoring Postgres 8.3 dump in 8.4

postgresql

I need to move a PostgreSql database from 8.3 Windows to 8.4 running on Linux (Ubuntu Server 12.04).

The restore aparently is ok, but when I try to show data in my program I get an error saying that some characters are not supported on WIN1252 encoding.

The original database has this properties:

Encoding: WIN1252
LC_CTYPE: English_United States.1252
LC_COLLATE: English_United States.1252

I can restore the database using LC_CTYPE = "C" and LC_COLLATE = "C", this way I get the unsupported characters in my program.

If I try to create the database using this command:

createdb -h 127.0.0.1 \
--encoding=WIN1252 \
--lc-ctype='English_United States.1252' \
--lc-collate='English_United States.1252' \
-T template0  -U postgres mydb

I get this error:

createdb: falló la creación de la base de datos:
ERROR:  el nombre de configuración regional «English_United States.1252» no es válido

First of all, how can I let my O.S. include this regional configuration?.

Best Answer

The Windows-1252 character set is known in Ubuntu by its other name CP1252.

Generally no corresponding locale is installed by default on Ubuntu because Unix folks prefer iso-8859-15 or utf-8.

However the charmap should be provided by the locales packages. Assuming this package is installed, you may issue a command like this to create the relevant locale:

sudo localedef -f CP1252 \ 
               -i /usr/share/i18n/locales/en_US \
               /usr/lib/locale/en_US.CP1252

Then run sudo /etc/init.d/postgresql restart for postgres to pick up the new locale, avoiding Postgresql 9.2 “invalid locale name” on Ubuntu 12.04.

Then you should be able to run createdb like you did in the question except for the locale name which is en_US.CP1252 instead of the Windows-ish English_United States.1252

WARNING ~ WARNING ~ WARNING

Note the purge keyword in the apt-get, it will remove any and all existing files AND data for both database instances, so be forewarned that your databases will be deleted along with the program. Make a backup of your data before proceeding!!!

If you don't want to loose the data but do want to ditch the program, replace "purge" with "remove".

Postgresql – What’s the default superuser username/password for postgres after a new install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Best Answer

Related Solutions

Linux – Postgres 8.3 and 8.4 in Ubuntu 9.10

WARNING ~ WARNING ~ WARNING

Postgresql – What’s the default superuser username/password for postgres after a new install

Related Topic