Thread: SQL_ASCII / LATIN1

SQL_ASCII / LATIN1

From

Aarni Ruuhimäki

Date:

14 January 2004, 10:23:55

Hi folks,

Bit of a situation here:

I have my own local and remote systems (7.3.3 says pg_config.h) initdb-ed with
-E LATIN1

#/usr/local/pgsql/bin/createdb test

gives

bash-2.05a$ psql -l
               List of databases
          Name           |  Owner   | Encoding
-------------------------+----------+----------
test                | postgres | LATIN1
...
template0               | postgres | LATIN1
template1               | postgres | LATIN1


#/usr/local/pgsql/bin/createdb test -E SQL_ASCII

gives

bash-2.05a$ psql -l
               List of databases
          Name           |  Owner   | Encoding
-------------------------+----------+----------
test                | postgres | SQL_ASCII
...
template0               | postgres | LATIN1
template1               | postgres | LATIN1

This is ok, I suppose.

On another remote machine, the system is also 7.3.3, I see:

sh-2.05a$ psql -l
            List of databases
       Name       |  Owner   | Encoding
------------------+----------+-----------
trouble       | postgres | SQL_ASCII
 ok1          | postgres | LATIN1
 ok2         | postgres | LATIN1
 ok3             | postgres | LATIN1
...
 template0        | postgres | SQL_ASCII
 template1        | postgres | SQL_ASCII

When I pg_dump -c trouble > trouble_dump, take the file down and
cat trouble_dump | psql trouble it shows as LATIN1 in the local listing. Then
I dump it again locally, upload and cat, it shows as SQL_ASCII.

Not sure if someone has re-inited the troublesome system at some point,
because it used to be ok. Can I see when and how somewhere ? The templates 0
and 1 are causing this ? I tried also dropping the db then create it with -E
LATIN1, but after cat from dump it still shows as SQL_ASCII.

Is there any other way to fix this than (upgrade and) re-init the whole system
?

Any advice is welcome. Thank you very muchos.

BR,

Aarni



--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com

Re: SQL_ASCII / LATIN1

From

Tom Lane

Date:

14 January 2004, 11:14:05

Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni.ruuhimaki@kymi.com> writes:
> When I pg_dump -c trouble > trouble_dump, take the file down and
> cat trouble_dump | psql trouble it shows as LATIN1 in the local listing. Then
> I dump it again locally, upload and cat, it shows as SQL_ASCII.

I believe the default encoding for a newly-created database is the same
encoding as template1 --- since the two installations were initdb'd with
different default encodings, the behavior you're describing is not too
surprising.  You can specify the encoding to use when you create a
database, though.

BTW, 7.3.3 has a serious known bug in the restart logic ... you ought to
update to 7.3.4 or 7.3.5 before you get bitten.

            regards, tom lane

Re: SQL_ASCII / LATIN1

From

Aarni Ruuhimäki

Date:

14 January 2004, 21:07:59

Hi,

And thanks again Tom + all the other good folks as well.

Happily on 7.4.1 now.

Here's what I did:

Dump all (as postgres):

bash-2.05a$ /usr/local/pgsql/bin/pg_dumpall -c > all_local_for_741_c


Postgres down (as root):

#kill -9 postmaster process number


Move old datadir out of the way:

#mv /usr/share/pgdata/ /usr/share/pgdata_old


New dir for the new install:

#mkdir /usr/share/pgdata


The above for postgres:

#chown postgres /usr/share/pgdata


Any other old stuff you might find out of the way (?):

#mv /usr/share/pgsql/ /usr/share/pgsql_old

#mv /usr/local/pgsql/ /usr/local/pgsql_old

(cruel ?)

#rm -R -f /usr/include/pgsql

#rm -R -f /var/lib/pgsql

(ruthless ?)

#rm /etc/rc.d/init.d/postgres


Back to business:

#gunzip postgresql-7.4.1.tar.gz

#tar -xf postgresql-7.4.1.tar

#cd postgresql-7.4.1

(next depends on your libs etc.):

#./configure --without-zlib --enable-locale

#gmake

.. Ready to install.

#gmake install

.. Install succesfull.

#su postgres

bash-2.05a$/usr/local/pgsql/bin/initdb -D /usr/share/pgdata -L
/usr/local/pgsql/share -E LATIN1

bash-2.05a$/usr/local/pgsql/bin postmaster -D /usr/share/pgdata -B 128 -N 256
&

bash-2.05a$psql -E template1 < all_local_for_741_c

Rock'n'roll !

BR,

Aarni


On Wednesday 14 January 2004 17:13, you wrote:
> Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni.ruuhimaki@kymi.com> writes:
> > When I pg_dump -c trouble > trouble_dump, take the file down and
> > cat trouble_dump | psql trouble it shows as LATIN1 in the local listing.
> > Then I dump it again locally, upload and cat, it shows as SQL_ASCII.
>
> I believe the default encoding for a newly-created database is the same
> encoding as template1 --- since the two installations were initdb'd with
> different default encodings, the behavior you're describing is not too
> surprising.  You can specify the encoding to use when you create a
> database, though.
>
> BTW, 7.3.3 has a serious known bug in the restart logic ... you ought to
> update to 7.3.4 or 7.3.5 before you get bitten.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: SQL_ASCII / LATIN1

From

Aarni Ruuhimäki

Date:

18 January 2004, 17:26:11

Hi,

And thanks again Tom + all the other good folks as well.

Happily on 7.4.1 now.

Here's what I did:

Dump all (as postgres):

bash-2.05a$ /usr/local/pgsql/bin/pg_dumpall -c > all_local_for_741_c


Postgres down (as root):

#kill -9 postmaster process number


Move old datadir out of the way:

#mv /usr/share/pgdata/ /usr/share/pgdata_old


New dir for the new install:

#mkdir /usr/share/pgdata


The above for postgres:

#chown postgres /usr/share/pgdata


Any other old stuff you might find out of the way (?):

#mv /usr/share/pgsql/ /usr/share/pgsql_old

#mv /usr/local/pgsql/ /usr/local/pgsql_old

(cruel ?)

#rm -R -f /usr/include/pgsql

#rm -R -f /var/lib/pgsql

(ruthless ?)

#rm /etc/rc.d/init.d/postgres


Back to business:

#gunzip postgresql-7.4.1.tar.gz

#tar -xf postgresql-7.4.1.tar

#cd postgresql-7.4.1

(next depends on your libs etc.):

#./configure --without-zlib --enable-locale

#gmake

.. Ready to install.

#gmake install

.. Install succesfull.

#su postgres

bash-2.05a$/usr/local/pgsql/bin/initdb -D /usr/share/pgdata -L
/usr/local/pgsql/share -E LATIN1

bash-2.05a$/usr/local/pgsql/bin postmaster -D /usr/share/pgdata -B 128 -N 256
&

bash-2.05a$psql -E template1 < all_local_for_741_c

Rock'n'roll !

BR,

Aarni


On Wednesday 14 January 2004 17:13, you wrote:
> Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni.ruuhimaki@kymi.com> writes:
> > When I pg_dump -c trouble > trouble_dump, take the file down and
> > cat trouble_dump | psql trouble it shows as LATIN1 in the local listing.
> > Then I dump it again locally, upload and cat, it shows as SQL_ASCII.
>
> I believe the default encoding for a newly-created database is the same
> encoding as template1 --- since the two installations were initdb'd with
> different default encodings, the behavior you're describing is not too
> surprising.  You can specify the encoding to use when you create a
> database, though.
>
> BTW, 7.3.3 has a serious known bug in the restart logic ... you ought to
> update to 7.3.4 or 7.3.5 before you get bitten.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org