Initializing the DBMS
(Note that this assumes no meta-database or anything exists at the present time.)
Mysql: Just start the mysqld daemon if he mysql meta-database doesn’t exist. (see below)
Postgresql: linux# su -c "initdb -D /path/to/data/dir" postgres
Starting the DBMS
Mysql: linux# service mysql start
Postgresql: linux# service postgresql start
Getting to an SQL prompt
Mysql: linux# mysql -u dbuser -pdbpass dbname
On a default database server install with us, the ‘root’ user can
usually connect without any password. Otherwise the authentication
credentials are often stored in /etc/mysql/bluebox.cnf. If the database
name is omitted, you still get a command prompt, but no database will
be selected.
Postgresql: linux# psql dbname dbuser
On a default database server install, only the ‘postgres’ user can
usually connect without any password. (The root user is usually
specifically not allowed to connect unless the ‘postgres’ user and
database are specified.) If a username or password are omitted, these
are assumed to be the same as the linux username running the command.
(So, if you get a message that no ‘root’ database exists… this is
why.)
Getting out of an SQL prompt
Mysql: mysql> quit
Postgresql: pgsql=# \q
Creating a new database
Mysql: mysql> CREATE DATABASE dbname DEFAULT CHARSET=utf8;
Postgresql: linux# createdb -U postgres -E UTF-8 dbname
Listing databases on the server
Mysql: mysql> SHOW DATABASES;
Posgresql: linux# psql -U postgres -l
in psql: postgres=# \list
Connecting to a database in psql
postgres=# \c dbname
Show tables in psql
first, connect to a database, then: postgres=# \dt