PostgreSQL for MySQL Administrators

Introduction

There will be day you have the pleasure to work with PostgreSQL.

But if you have worked with MySQL before, there will be a lot obscuring commands, which won’t make sense at the beginning. This post is trying to help you with the equivalents if you use MySQL in your daily work.

Logging in

 

MySQL

MySQL with current user on localhost:

other user on another host:

If you already know to which database you need to connect, pass it as the last argument.

It’s not recommended to pass the password as a command line option, but just for the sake of completeness:

(That ain’t my password ;))

PostgreSQL

Logging in with the superuser on localhost:

Connect to remote host with user ‘web_admin’:

HINT: psql will always ask for a password if there is no trusted connection configured, but if you know the server requires authentication always pass the option

, otherwise psql will waste one connection to check if authentication is required.

Command table

I will update the table from time to time, whenever I discover differences :)

If you got more commands, don’t hesitate to use the comments, and I will add them to the table.

Below you can see the MySQL command on the right and the matching PostgreSQL command on the right.

DescriptionMySQL CommandPostgreSQL Command
Show all Databasesshow databases;\list
Connect to a databaseuse database1;\c database1
Show tables in databaseshow tables;\dt
Describe tabledescribe table1;\d+ table1;
Show tables with more informationN/A\dt+
Show tables in schemaN/A\dt schema_xyz.*
Show schemas in databaseN/A\dn
show running queriesshow processlist;select * from pg_stat_activity;
show latest executed query in editoredit;\e

Backup

 

MySQL

Authentication required? Use the same options(-u, -p, -h) as you’d like to connect to the MySQL-Server
If you are familiar with MySQL you know how it works:

or just backup all databases:

Since it’s raw SQL it could eat a lot of disk space. If this is a matter for you, you could pipe it trough gzip.

You can see a full example with single dumps for every database here.

PostgreSQL

So how to achieve the same results in postgres?

Authentication required? Use the same options as you’d connect to the database.

Dump a single database named wordpress as user web_admin:

Same thing as with mysql, it is raw mysql and could eat a lot of disk space.
With PostgreSQL you do NOT need to gzip the output. You can just enable the postgres custom format(-Fc), which saves
the dump in a packed format.

Can I dump all databases? Sure, just replace pg_dump with pg_dumpall and skip the database name.

Restore

MySQL

Authentication required? Use the same options(-u, -p, -h) as you’d like to connect to the MySQL-Server

Restoring in MySQL is quite easy.
Just take the dump-file and redirect its about to the mysql-client, like this:

CAUTION: in the head of the dumpfile you’ll see, there are DROP TABLE statements. So be sure to know what you are doing, when restoring a dump.

If you zipped you dump, the command looks a bit different:

If you had dumped all your databases(–all-databases), there’s no need to provide the database.

PostgreSQL

Restoring is very simple in PostgreSQL.

At least if choose a plain sql dump:

If you dumped with custom format, you need to use pg_restore and pipe it to psql:

Go further

There are a lot more options for both, MySQL and PostgreSQL. I hope this post helps you at least a bit.

Got a question? use the comments and have fun!

Thanks to the guys on the subreddit https://www.reddit.com/r/commandline/ for their suggestions!

2 thoughts on “PostgreSQL for MySQL Administrators

  1. Really nice article, keep it up. What is interesting is with comparing just a few commands like this we can see that Postgres is ahead of MySQL in this situation as well.

    I have a few suggestions. For pg_dump I would recommend that you add the “-z” flag which also uses gzip compression to compress the custom dump format, which makes the dump even smaller.

    Also you don’t have to pipe pg_restore to psql, you can just pass “-d dbname” to pg_restore and it will restore directly into that database. And the biggest reason why you should use pg_restore without any piping is because it supports restoring the database with parallel jobs which makes the restore a few times faster. This can be specified with “-j N”, where N is the number of parallel jobs.

Leave a Reply

Your email address will not be published. Required fields are marked *