In this blog post, I’ve associated several PostgreSQL commands with what I know in MySQL. Below is a table of some of the frequently used commands.
| MySQL | PostgreSQL | Description |
| mysqldump | pg_dumpall | extract a PostgreSQL database cluster into a script file |
| mysqldump | pg_dump | extract a PostgreSQL database into a script file or other archive file |
| N/A | pg_restore | restore a PostgreSQL database from an archive file created by pg_dump |
| N/A | pg_top | pg_top is a PostgreSQL interactive monitoring utility, similar to the Unix top utility, providing a dynamically updated view of activity within a PostgreSQL database |
| mysql | psql | PostgreSQL interactive terminal |
| Exit Mysql {quit|exit| [ctrl-c]} | Exit Postgres\q | quit psql, |
| show databases; | \l or \list | View all of the defined databases on the server |
| use [dbname]; | \c [dbname] | Connect to a database |
| show tables; | \dt or \dt+ | List all the tables in the current schema |
| describe [tablename]; | \d [tablename] | Shows the columns, types, modifiers, indexes, and tables referenced by keys. |
| show create table [tablename]; | No direct equivalent, use below command from shell: pg_dump -st tablename dbname | This will provide the sql used to create a table. |
| select * from mysql.user; | select * from pg_user; \du | Lists all database roles |
| show full processlist; | select * from pg_stat_activity; | Show what queries are currently running in the DB |
| show variables; | show all; | Displays the current setting of run-time parameters |
| show engine innodb status\G | SELECT * FROM pg_stat_activity; SELECT * FROM pg_stat_database; SELECT * FROM pg_stat_user_tables; SELECT * FROM pg_stat_user_indexes; SELECT * FROM pg_locks; | There is no central place in PostgreSQL to get all of the information obtained by running show engine InnoDB status in MySQL. However, there are a number of queries you can run to get roughly equivalent data. |
| show slave status\G | select * from pg_stat_replication; select now() – pg_last_xact_replay_timestamp() AS replication_delay; | Monitor replication |
| \G | \x | Sets or toggles expanded table formatting mode |
Cheers!
Leave a Reply