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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni.ruuhimaki (AT) kymi (DOT) com> writes:
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

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 Ruuhimäki | Megative Tmi | KYMI.com

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