psql is a command line client for the PostgreSQL Database Engine. If you install postgreSQL on Debian psql is installed by default. After you switched to the postgteSQL user:
you can access the client with this simpel command:
and you can exit with this command:
You can set a password with this command inside of the psql client:
ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'postgres';
you can call the help with this command:
It’s a mess! I think more “NOT SEXY” is not possible! MySQL is maybe not as good as PostgreSQL, but from the usability it is 10 times better. In MySQL the command for exit is “exit” not “\q” and for showing the databases the command is “show databases” and for showing tables “show tables”. Much more intuitive than PostgreSQL!
Installing PostgreSQL 8.3 on Debian Linux is pretty easy. With this command it is done.
apt-get install postgresql-8.3
Now the DB system is installed under:
You can see the status of the RDBMS with this command:
You can start it
and you can stop it
After the successful installation there is also a “postgres” user on your system. You can set the password of this user with this command:
Now you can login as postgres user with:
And with this command you can create a database:
Your DB ist up and running.
Today I migrated a 10 MB database from MySQL to PostgresSQL. I used the “ESF Database Migration Toolkit – Standard 6.5.06”. It works pretty good for me.
I access the database with a spring/hibernate based java application. After some litle changes on the applicationContext.xml I could successfully start my app and access the postgresSQL database.
When I tried to enter new data through the java application I got some errors:
Caused by: java.sql.BatchUpdateException: Batch-Eintrag 0 insert into
The reason for this error is that postgresSQL uses sequences to generate IDs. The migration toolkit from ESF created for every table a separate sequence. By default hibernate creates one sequence for all tables.
After I customized the hibernate mappings to use the sequences created by the migration toolkit from ESF, it works fine for me. Here is the right mapping for a custom sequence.
<id name="id" type="long" column="`ID`">
It is absolute important that the sequence name is spelled in small letters, in the postgresSQL database.