Hacker News Reader

Comment by yAnonymous | Hacker News Reader

The reason I never made the switch to Postgres is that I haven't found a good tutorial that explains how to do the basic things I do with MySQL on the CLI with Postgres.

It starts with logging into the DBMS. Importing/exporting/dumping databases from/to a file? Running queries from the shell? User management? Backups?

View on HN

Hi, just have a look here : http://phili.pe/posts/postgresql-on-the-command-line/

That's the link in this post so yeah, I read that. It covers most of the stuff that happens inside the Postgres CLI which is good, but I also need to know about the things I can do directly from the Linux shell.

How to backup a Postgres DB?

    mysqldump -u user -p DB > dump.sql

Import a DB from the Linux command line?

    mysql -u user -p DB < dump.sql

How to run a command directly from a shell script?

    mysql -u [user] -p[pass] -e "[mysql commands]"

When managing systems, this stuff is more important to me than the actual Postgres syntax.

Backup: http://www.postgresql.org/docs/current/static/app-pgdump.htm...

In short:

  pg_dump -Fc mydb > db.dump

Restore: http://www.postgresql.org/docs/current/static/app-pgrestore....

In short:

  pg_restore -C -d postgres db.dump

Command directly from the shell:

  psql -c "psql command"

and the default dumps are simple sql files, which you can actually pipe to another server.

pg_dump -C mydb | psql -h ${some_other_server} mydb

Reply on news.ycombinator.com