Sql admin

For many people taking the first step of moving from mysql to postgres is a large stride because the psql client takes some getting used to. To help bridge this gap we will look at some common mysql admin tasks carried out with the command line client and look at their counterparts in postgres.

Action mysql command pgsql command
List databases SHOW DATABASES SELECT dbname FROM pg_database;
Use a different database USE dbname \cdbname
Obtain list of tables SHOW TABLES \dt
View table structure DESC tablename \d tablename

Navigation

Online convertor (http://www.raditha.com/mysql/mysql2pgsql.php)
Making up the numbers
Basic Administration
Strange characters
Links (http://www.raditha.com/mysql/links.php)
Date and Time

Migration (http://www.raditha.com/postgres/)


In both systems the output produced from the commands above are similar (though their presentation may differ). However certain other tasks such as user and privilege management display marked differences.

In mysql new users have to be created with the GRANT command while in postgres you need to make use of the CREATE USER command intead and follow up with a GRANT command. On the other hand recent versions of mysql does provide support for the DROP USER command, however in order to use it you have to first REVOKE PRIVILEGES. In other words only a user without privileges can be removed in this manner.


Action mysql command pgsql command
Create a new user and grant privileges GRANT ALL PRIVILGES ON dbname.* TO user@hostname IDENTIFIED by 'password'; CREATE USER username WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE dbname to username'
Retrieve user privileges SHOW GRANTS FOR user \dp
An exact match is not available in postgres.
Delete user REVOKE ALL PRIVILEGES ON dbname.* FROM username;
followed by
DELETE FROM mysql.user WHERE user='username';
FLUSH PRIVILEGES;
DROP USER username;
provided the user does not own any databases.


An extension to the sql standard in mysql allows you to add a suffix of '@hostname' to the username to grant permissions to remote users, with postgresql you need to edit your pg_hba.conf file to allow/deny remote connections. Despite that the pg_hba.conf method is superior because it gives you plenty of flexibility and options not supported by the competition.

Copyright © Raditha Dissanayake 2013