As 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.
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'
I'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.
yep. 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.
Semi-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.
This 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)).
This 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.
on the bash command line before starting psql should fix this. This was one of those things I figured out several years after I started using bash that would have made my life much less annoying.
Note 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.
Funny, I just tested those yesterday. And they none of them seemed to work. And I think I had installed the newest version for the test. Anyways, I stand corrected. It's great to see some of these working now as the tab-completion is definitely better than psql's.
\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
It 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?
Thanks 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 :)
So 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?
We 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.
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?
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.
csvkit 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.
44 comments
[ 3.8 ms ] story [ 82.1 ms ] threadThat hasn't been my experience with MySQL and other SQL-flavors, nor with the NoSQL projects like MongoDB and CouchDB.
Weird I know, but it works. Still psql is a superior client IMHO.
MySQL also has \c which I find tremendously useful, when you screw something up mid-query.
I found this: http://stackoverflow.com/questions/2024884/commandline-overw... but it didn't seem to help.
on the bash command line before starting psql should fix this. This was one of those things I figured out several years after I started using bash that would have made my life much less annoying.
http://www.mail-archive.com/bug-bash@gnu.org/msg03562.html
https://blogs.oracle.com/dp/entry/why_bash_doesn_t_work
- 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
- \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
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
http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/
http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...
1: http://postgresweekly.com
2: http://www.postgresguide.com
@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?
[1] https://github.com/influitive/apartment
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.
It starts with logging into the DBMS. Importing/exporting/dumping databases from/to a file? Running queries from the shell? User management? Backups?
How to backup a Postgres DB?
Import a DB from the Linux command line? How to run a command directly from a shell script? When managing systems, this stuff is more important to me than the actual Postgres syntax.In short:
Restore: http://www.postgresql.org/docs/current/static/app-pgrestore....In short:
Command directly from the shell:pg_dump -C mydb | psql -h ${some_other_server} mydb
Import with csvsql http://csvkit.readthedocs.org/en/0.3.0/scripts/csvsql.html and export with sql2csv http://csvkit.readthedocs.org/en/latest/scripts/sql2csv.html
[1]: https://github.com/fphilipe/psql2csv