|
MySQL (mysql)
|
Postgres (psql)
|
Extra Notes
|
|
\e
|
\e
|
Edit the buffer with external editor. Postgres also
allows \e filename which will become the new buffer
|
|
\g
|
\g
|
Send current query to the server
|
|
\h
|
\h
|
Gives help — general or specific
|
|
\n
|
\pset pager off
|
Turns the pager off. For PGSQL, the pager is only used when
needed based on number of rows; to force it on, use \pset pager
always
|
|
\d string
|
No equivalent
|
Changes the delimiter
|
|
\c
|
\r
|
Clears the buffer
|
|
\p
|
\p
|
Print the current buffer
|
|
\q or Quit or exit or [ctrl-c]
|
\q
|
Quit the client mysql> or psql>
|
|
\r [dbname] [dbhost]
|
\c [dbname] [dbuser]
|
Reconnect to server
|
|
\s
|
No equivalent
|
Status of server. Some of the same info is available from
the pg_settings table
|
|
\u dbname or Use [db_name]
|
\c dbname
|
Connect to a database or use a different database.
|
|
\w
|
No equivalent
|
Do not show warnings. Postgres always shows warnings by
default
|
|
\C charset Change the charset
|
\encoding encoding Change the encoding
|
Run \encoding with no argument to view the
current one
|
|
\t Stop teeing output to file
|
No equivalent
|
However, \o (without any argument) will stop
writing to a previously opened outfile
|
|
\G
|
\x
|
Display results vertically (one column per line). Note
that \G is a one-time effect, while \x is a
toggle from one mode to another. To get the exact same effect as \G in
Postgres, use \x\g\x
|
|
\R string
|
\set PROMPT1 string
|
To Change the prompt. Note that the Postgres prompt cannot be
reset by omitting an argument. A good prompt to use is:\set PROMPT1 '%n@%`hostname`:%>%R%#%x%x%x
'
|
|
\P pagername
|
Environment variable PAGER or PSQL_PAGER
|
Change the current pager program
|
|
\. filename
|
\i filename
|
Include a file as if it were typed in.
|
|
\T filename
|
No direct equivalent
|
Sets the tee output file. Postgres can output to a pipe, so
you can do: \o | tee filename
|
|
\W
|
No equivalent
|
To Show warnings. Postgres always show warnings by default
|
|
\?
|
\?
|
Help for Internal Commands.
|
|
\#
|
No equivalent
|
To rebuild tab-completion hash. Not needed in Postgres, as
tab-completion in Postgres is always done dynamically
|
|
\! command
|
\! command
|
To execute a shell command. If no command is given with
Postgres, the user is dropped to a new shell (exit to return to psql)
|
|
Timing is always on
|
\timing Toggles timing on and off
|
|
|
No equivalent
|
\t Toggles “tuple only” mode
|
This shows the data from select queries, with no headers or
footers
|
|
show tables; List all tables
|
\dt or /dt+
|
Many also use just \d, which lists tables, views,
and sequences
|
|
desc tablename;
|
\d tablename
|
Display information about the given table.
|
|
show index from tablename;
|
\d tablename
|
Display indexes on the given table. The bottom of the \d tablename output
always shows indexes, as well as triggers, rules, and constraints
|
|
show triggers from tablename;
|
\d tablename
|
Display triggers on the given table. The bottom of the \d tablename output
always shows indexes, as well as triggers, rules, and constraints
|
|
show databases;
|
\l
|
List all databases
|
|
No equivalent
|
\dn
|
To List all schemas. MySQL does not have the concept of
schemas, but uses databases as a similar concept
|
|
select version();
|
select version();
|
Show backend server version
|
|
select now();
|
select now();
|
Show current time. Postgres will give fractional seconds in
the output
|
|
select current_user;
|
select current_user;
|
Show the current user.
|
|
select database();
|
select current_database();
|
Show the current database.
|
|
show create table tablename;
|
No equivalent
|
Output a CREATE TABLE statement for the given table. The
closest you can get with Postgres is to use
pg_dump --schema-only -t tablename
|
|
show engines;
|
No equivalent
|
To List all server engines. Postgres does not use separate
engines
|
|
CREATE object ... Create an object:
database, table, etc.
|
CREATE object ... Mostly the same
|
Most CREATE commands are similar or identical. Lookup specific
help on commands (for example: \h CREATE TABLE)
|
|
SHOW INDEX FROM some_table;
Show indices of some_table (in case of MySQL)
|
\di
Show all indices of database (PostgreSQL)
|
Display Indices.
|
|
mysqldump
|
pg_dumpall
|
pg_dumpall is designed to dump all the databases and invokes
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
|
|
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 server pg_top shows similar things but is laid out
more similarly to the native linux “top” program.
|
|
mysql
|
psql
|
These commands allow to access the CLI utility.
|
|
Passing query with mysql (-e for execute):
mysql -e “select * from table_name;”
|
Passing query with postgres (-c for command):
psql -c “select * from table_name;”
|
Passing sql into the utility.
|
|
show databases;
|
\l or \list
|
This lists the databases on the server instance you are
connected to or that you have access to.
|
|
select * from mysql.user;
|
select * from pg_user; or\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 the queries that are currently running and
how long they have been running for.
|
|
show variables;
|
show all;
|
This will show all 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 the
information obtained by running show engine innodb status in mysql. There are
several 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 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 location to know if the server is
behind.
|
|
stop slave;
start slave;
|
select pg_xlog_replay_pause();
select pg_xlog_replay_resume();
|
Pause/resume/stop/start replication on the slave node
|
|
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE
'VIEW';
|
\dv
\dv schema_name.*
|
List views
|
|
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
|
\dt+
\dv+
\d+ table_name
Get extended information (add + to any command)
|
In MySQL, The optional EXTENDED keyword causes the output to
include information about hidden columns that MySQL uses internally and are
not accessible by users.The optional FULL keyword causes the output to
include the column collation and comments, as well as the privileges you have
for each column.
|
