8 ms·
PostgreSQL on the Command Line
- yes_or_gnome 11y agoAs someone that is not a DBS expert, I like how easy it is to use PostgreSQL. Sure, you do need to conceptually know how a relational database works before you jump into it's interactive CLI. But, once you have that knowledge, you can learn a lot about a particular database by knowing just one command. That is '\?'. That hasn't been my experience with MySQL and other SQL-flavors, nor with the NoSQL projects like MongoDB and CouchDB.
- rockdoe 11y agoMySQL also had a usable commandline. Not sure it had shortcuts like \d+ etc though.
- rimantas 11y agoThis is what MySQL gives you for \?: List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents'
- deckard1 11y agoI've always found MySQL's CLI vastly easier to use than PostgreSQL. The \G command alone is godsend. And the ASCII formatting makes everything much more readable to me.
- postila 11y agohave you tried "\x on" and "\x auto" in psql?
- agopaul 11y agoIn MySQL you append \G at the end of the query instead of ; to get the same result as \x. Weird I know, but it works. Still psql is a superior client IMHO.
- deckard1 11y agoyep. It's not the same at all. The format is harder to read, and the point of \G is you can do it without toggling. Say you run a query. Turns out, you need vertical format to see the entire contents. Just hit up arrow, replace the ';' at the end with '\G' and run the query again. MySQL also has \c which I find tremendously useful, when you screw something up mid-query.
- tcas 11y agoSemi-related: Has anyone had issues with psql wrapping long lines incorrectly? Specifically it starts overwriting the current line. I'm using zsh on OS X using the default terminal, though the behavior also appears when SSHing to a remote server which uses bash. I found this: http://stackoverflow.com/questions/2024884/commandline-overwrites-itself-when-the-commands-get-to-long http://stackoverflow.com/questions/2024884/commandline-overw... but it didn't seem to help.
- _yy 11y agoSame issue for me. Probably readline-related.
- pilif 11y agoThis only happens to me if I change the window size after launching psql. If I settle on a window size before launching it, then it never happens.
- colanderman 11y agoThis only happens to me if I change the window size while viewing query results in less(1) while inside of psql. Resizing the windows at the psql prompt itself always works for me (and in fact fixes any issues caused by resizing while inside of less(1)).
- olefoo 11y agoSet your pager to let the overflow go off the screen: export PAGER='less -S' And the lines will not wrap in the terminal, when you have a lot of output.
- fuhrysteve 11y agoThis here is some of the beauty of linux - psql simply pipes the output to less - a tool that is made exclusively to handle these sorts of "look at a bunch of text on a terminal" problems. Most of the problems less exists to solve would likely be deemed out of scope for a single-purpose utility like psql.
- leftnode 11y agoGreat article, I love psql. I also add \timing to my .psqlrc to see how long every query took.
- kawera 11y agoPgcli is an alternative command line tool with auto-completion and syntax highlighting. Open source and works well: http://pgcli.com/ http://pgcli.com/
- fphilipe 11y agoSure, you can use pgcli instead of psql. But it doesn't replace psql. In fact, most tasks described in the article cannot be accomplished with pgcli. - it can't list schemas with \dn - it can't list materialized views with \dm - \dt <star>.users doesn't work as it does in psql, i.e. it just lists all tables due to the * instead of listing users tables in all schemas - it can't use your editor with \e - it can't input existing files with \i - it can't redirect the output with \o - it can't extract data with \copy
- kawera 11y agoTrue, it's not a replacement for psql but a great little tool for examining databases and playing with queries.
- macobo 11y agoNote that the project is quite new and has a really active dev base - if you use those features, feel free to submit a PR (or add an issue) and it's pretty sure to get accepted.
- voltagex_ 11y agoHas someone raised these yet? Otherwise I will.
- thelibrarian 11y agoOut of curiosity, I just tried out v0.19.2 (the latest), and some of these now work: - \dn works - \dm does not - \dt <star>.users now appears to work as per psql - \e works - \i works - \o does not - \copy does not
- 11y ago
- jastr 11y ago\e is the most amazing shortcut for big ugly queries! I found that I often want to do basic things, like see the schema and some sample values, maybe even the distribution of values. So, I ended up building a web UI, to do all of these in just a few clicks. It's also a good way for non-developers to run basic queries - https://getvql.com https://getvql.com
- shrikant 11y agoIt looks interesting, but there's no information on how I can take it out for a spin. Do you have any additional information, documentation or a screencast?
- jastr 11y agoScreencast - https://getvql.com/demo_citi_bike.php https://getvql.com/demo_citi_bike.php
- glogla 11y agoThat's pretty cool. I'd like to see pricing before writing email, though.
- craigkerstiens 11y agoHere's a few, partially shameless plugs, of similar articles that have some overlap but hit some other niceties of psql. http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/ http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/ http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/ http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...
- fphilipe 11y agoThanks Craig, in those 4 years I've been using Postgres extensively your blog posts, talks, newsletter [1], guide [2] have been invaluable learning resources. Now I'm trying to contribute back to the community, too :) 1: http://postgresweekly.com http://postgresweekly.com 2: http://www.postgresguide.com http://www.postgresguide.com
- yAnonymous 11y agoThe 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?
- MitsuTomoe 11y agoHi, just have a look here : http://phili.pe/posts/postgresql-on-the-command-line/ http://phili.pe/posts/postgresql-on-the-command-line/
- yAnonymous 11y agoThat'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.
- AlterEgo20 11y agoBackup: http://www.postgresql.org/docs/current/static/app-pgdump.html 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.html 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"
- y4mi 11y agoand 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
- aidos 11y agoSo satisfying when you read an article that just happens to be pitched at exactly the stage of learning you're at. Every single bit of advice in here is something I can use today without any thought. @fpilipe, you mention you use one schema per customer. I'm curious as to how many people do this and how well it works for them. Any thoughts?
- pjungwir 11y agoIt's not my usual way of doing things, but there is a Rails gem[1] for this that looks pretty nice. [1] https://github.com/influitive/apartment https://github.com/influitive/apartment
- fphilipe 11y agoWe use the apartment gem pjungwir mentioned. I guess it works well if you have a small number of customers, each with lots of data. The other way around, lots of customers with little data would not scale, I'd say. As with any technique, there's pros and cons. Pros: - When you enter a psql session, you `SET search_path TO customer;` and that's it. No customer_id column in any table you have to remember setting in your WHERE clause. - Same thing for rails console. At the beginning of the session you choose which customer you want to operate on. - Similar for web requests. You just activate the right customer (e.g. based on subdomain) and that's it. Instead of `current_customer.posts.find(params[:id])` you can now simply do `Post.find(params[:id])`. - Data is completely separated. It's almost impossible to have a bug causing data to spill from one customer to the other, which is quite high chance when you always have to remember to load data through the current customer. Cons: - Number of objects (tables, indexes, sequences) is multiplied by number of customers. - Adding a new customer requires `pg_dump` to be present as it dumps the public schema (the blueprint), creates a new schema, and loads the dump into that schema. - Migrations take longer as there are more objects to touch. In our case, the pros outweigh the cons. One further advantage of the separate schemas is that, in theory, we could at any time start using completely separate databases, one for each customer or just one additional for a much larger customer that's eating up all resources on the DB.
- aidos 11y ago
- nanoojaboo 11y agoI find many of these articles very samey-samey. Wish they would step through more advanced features (when and how to use etc) of pg
- DerKobe 11y agoI'm a huge fan of JetBrains' 0xDBE, but nothing can replace knowing your command line magic. Very nice guide!
- ThomasG77 11y agoFor CSV export and import from any DB including PostgreSQL, you should look at CSVKit for this purpose. Import with csvsql http://csvkit.readthedocs.org/en/0.3.0/scripts/csvsql.html http://csvkit.readthedocs.org/en/0.3.0/scripts/csvsql.html and export with sql2csv http://csvkit.readthedocs.org/en/latest/scripts/sql2csv.html http://csvkit.readthedocs.org/en/latest/scripts/sql2csv.html
- fphilipe 11y agocsvkit is a great tool, use it everyday. Haven't used the sql2csv tool though. For exporting, I'd say that psql2csv [1], which is mentioned in the article, is still easier to use. It has the same interface as psql, except for an additional argument, the query. And, it doesn't depend on python, which might not be available. [1]: https://github.com/fphilipe/psql2csv https://github.com/fphilipe/psql2csv