[psql] Command Equivalents in Postgres Coming From Mysql

Mysql	Postgres	Description
mysqldump	pg_dumpall	pg_dumpall is designed to dump all of the databases and calls pg_dump to do it. It can also be used to dump global values like roles and tablespaces.Example with mysql: mysqldump –all-databases > /path/to/file.sql Example with postgres: pg_dumpall > /path/to/file.sql
mysqldump	pg_dump	pg_dump is used for dumping individual databases.Example with mysql: mysqldump mydatabase > /path/to/file.sql Example with postgres: pg_dump mydatabase > /path/to/file.sql
n/a	pg_restore	pg_dump is capable of dumping to multiple formats with the -F option.  The default option is as a raw sql file.  If you wanted to dump as postgres’s “custom” format, you could add -Fc as options before the database name when using pg_dump.pg_restore is designed to restore from output files generated in this fashion. I am not aware of a mysql equivalent for pg_restore, but it reminds me more of a restore of a binary type file that you would do with something like sql server.
innodb_top	pg_top	innodb_top does not ship with mysql and is a third party executable.  It shows you things like inserts per second, updates per second, transactions per second and gives a good overview as to what is going on with the serverpg_top shows similar things but is laid out more similarly to the native linux “top” program.
mysql	psql	This is the command to enter the CLI utility.  You can also pass sql into the utility from the linux command line like shown below.Passing query with mysql (-e for execute): mysql -e “select 1;” Passing query with postgres (-c for command): psql -c “select 1;”
Exit Mysql{quit|exit|[ctrl-c]}	Exit Postgres\q	I completely felt like a noob the first time I got into the psql CLI.  I tried entering all of the things I would typically enter to exit mysql, or even terminals and other programs to no avail.  I had to google search it and discovered that postgres does just about everything with slash commands.  \q quits postgres and returns you to your shell.
show databases;	\l or \list	This lists the databases on the server instance you are connected to or that you have access to.
use [dbname];	\c [dbname]	Connect to a database or put yourself in a database context
show tables;	\dt or \dt+	Show the tables in the database context you are connected to. The plus adds size data and description fields
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 give the sql used to create a table.
select * from mysql.user;	select * from pg_user; \du	Shows all users and their global permissions.  Postgres lists the permissions as a comma separated string under a filed called attributes.  Mysql shows a boolean value for each of the possible permissions.
show full processlist;	select * from pg_stat_activity;	This will show all of the queries that are currently running and how long they have been running for.
show variables;	show all;	This will show all of the current values for the variables. Postgres even offers a brief description of what each variable is.
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 postgres to get all of the information obtained by running show engine innodb status in mysql.  There are a number of queries you can run to get roughly equivalent data though.
show slave status\G	select * from pg_stat_replication; select now() – pg_last_xact_replay_timestamp() AS replication_delay;	Shows replication information. On idle write masters you can errantly see replication report as behind or lagging. This is actually showing you the current timestamp minus the timestamp of the last item applied on the slave. If the master hasn’t written anything, the slave has not applied anything and can show you that it is behind. It is recommended to use a written timestamp from cron every minute to gauge replication. This achieves 2 things, it will guarantee regular writes to the master which will replicate to the slave, and monitoring can look to the timestamp in a particular location to know if the server is behind. This is similar to the heartbeat checks that percona recommends in mysql.
\G	\x	Sometimes it is nice to have information displayed in a non-row format. Mysql achieves this with using a “\G” at the end of the query and it will output each row of database data is a key-value pair. Postgres calls this expanded output mode. You can toggle expanded output mode to on by typing “\x[enter]” and then running your query normally. This is a session setting, so if you want to go back to row format, you can toggle it back to off with the same action.
stop slave; start slave;	select pg_xlog_replay_pause(); select pg_xlog_replay_resume();