Обсуждение: SQL_ASCII / LATIN1
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
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
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
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