9 ms·

How I work with Postgres – psql, My PostgreSQL Admin

149 points by craigkerstiens 14y ago

kochb 14y ago
Just found TeamPostgreSQL (http://www.teampostgresql.com/ http://www.teampostgresql.com/) earlier today, by far the best I've ever worked with. Not that the other tools have set a very high bar. pgAdmin crashes constantly, and you can't sort columns by clicking on them. Navicat doesn't run properly on Linux, you have to mess around with wine. phpPgAdmin has a single DB host is hard coded into a config file and just feels antiquated. One major drawback of TeamPostgreSQL so far: it doesn't support SSL connections.
- staz 14y ago
  It doesn't support SSL connexion to the database or it doesn't support HTTPS ? (And does it work if you put it behind a proxy?)
jaytaylor 14y ago
I've frequently given the same advice to my co-workers. The GUI's for Postgres are all lackluster, and learning to use psql with \d seems like the best way to go. It takes a little time to get used to, but once acclamated it blends into the background and works great.
Tashtego 14y ago
Great article but I can't get his advice for using Sublime Text as the editor to work. \e will open a new file, but saving/quitting doesn't ever run the query.
- stevvooe 14y ago
  'subl -w' is the correct incantation.
  - xentronium 14y ago
    Last time I checked, it didn't work in linux iff there were other sublime windows open. See http://stackoverflow.com/questions/14598261/making-sublime-text-2-command-on-linux-behave-as-it-does-on-macos-x http://stackoverflow.com/questions/14598261/making-sublime-t...
  - Tashtego 14y ago
    Thanks- that works!
msluyter 14y ago
I've been using Squirrel and it seems ok; fairly decent most of the time, actually. (Anyone else have any thoughts on Squirrel?) I've never used psql though. I'll look into it.
- gburt 14y ago
  Squirrel, like all Java software I have ever used, has this bad habit of getting in to an inconsistent state where it needs to be restarted to use.
ScotterC 14y ago
Can't wait till Sequel Pro supports Postgres. Was really excited to see them publicly start down the path of implementing it last September. http://stuconnolly.com/blog/sequel-pro-postgresql-support/ http://stuconnolly.com/blog/sequel-pro-postgresql-support/
- johne20 14y ago
  I agree. I honestly think this is the biggest thing slowing adoption of psql. It is the one thing I miss from mysql.
dinkumthinkum 14y ago
That's cute but, let's be honest, viewing query results for anything but trivial schemas or amounts of data with psql is somewhat close to impractical.
- Roboprog 14y ago
  Dump the result set as a table into a file and refresh "the file" in a browser? Having 2 windows open is about the same as having 2 panels in one of the DB "Monties". http://catb.org/jargon/html/M/monty.html http://catb.org/jargon/html/M/monty.html
  - Roboprog 14y ago
    That said, the sql*pro CLI that Oracle provides for their DB SUCKS (!!!) rotten eggs, and gives people the impression that a CLI is useless for database work.
    - sehrope 14y ago
      Yes the CLI for sql*plus does suck. When I am using it (pretty rare these days) I use rlwrap to make it suck less. Command history is pretty convenient too. Here's my ~/bin/sqlplus shell script pointing to the Oracle instant client install: #!/bin/sh OIC_DIR=$HOME/opt/oracle-instant-client export LD_LIBRARY_PATH="${OIC_DIR}:$LD_LIBRARY_PATH" export TNS_ADMIN="${OIC_DIR}" rlwrap "${OIC_DIR}/sqlplus" "$@" Bonus: You can use a tnsnames.ora file with the instant client if you put it in the instant client directory and export the environment variable in the script above.
      - Roboprog 14y ago
        That's right, it's been a while. "pro" was the C preprocessor, "plus" was the gawdhawful CLI. Thanks for the tip on rlwrap, in case I have to use that thing again. I'm assuming it provides GNU readline support on top of the base tool.
        14y ago
drewda 14y ago
Navicat is a decent way to deal with both Postgres and MySQL (among many other database engines) with the same interface. All the different ways Navicat packages its products are unnecessarily confusing. Premium Essentials ($20) is almost certainly what you want: http://www.navicat.com/en/products/navicat_essentials/essentials_overview.html http://www.navicat.com/en/products/navicat_essentials/essent...
- SkyMarshal 14y ago
  Just downloaded their trial edition a few days ago for an upcoming project but haven't started playing with it yet. Glad to hear good things about it.
alternize 14y ago
i'm pretty happy with ems' sql manager - while not free, it has everything i ever needed from a postgres gui. working with psql to query large data sets seems a bit tedious to me - i prefer a multiwindow gui application with a decent tabular view that i can browse through. i heavily use psql for maintenance stuff tho.
olefoo 14y ago
I'm partial to SQL-mode in emacs which allows you to start psql in a comint style buffer, send queries from an edit buffer and capture them to output buffers; it used to be a pain to set up but these days it mostly just works. And by accident or design, there is almost no interference between the psql commands and the editor.
Roboprog 14y ago
But, but, but! If I use the CLI instead of a GUI, people might start asking me to put what I am doing into a file and save it in revision control. They might even ask me to make a function (procedure) out of some of it so it can be extended or reused. Then where would we be -- how can I claim that DBA-ing is magic? :-)
- Roboprog 14y ago
  Funny: this got upvoted, then immediately downvoted. Come on, tell the truth: how many of you have had The Great and Powerful Oz for a DBA? (there's a wonderful BOFH episode about the BOFH vs a DBA - read when in a foul mood some time for a chuckle)
- papsosouid 14y ago
  I find your characterization very puzzling. It is the DBAs I've worked with who want to use revision control, who want to use correct database schemas, who want data integrity, who want to use stored procedures. They are the ones fighting against developers who never bothered to learn how to use a relational database and push the mysql/php style of "just make your app the database and the database is just a file full of inconsistent data, don't do joins, don't use view, don't use stored procedures or functions, don't use triggers, etc."
  - Roboprog 14y ago
    Either you have been lucky, or my coworkers and I have been unlucky. YMMV. Fortunately, most all the DBAs I've worked with do in fact value data integrity, but, don't seem to care about other development practices like automated DB version migrations or reducing repetition.
guylhem 14y ago
I do the same, but I have one thing I miss - an easy way to send the output out for graphs. Tweaking and playing with gnuplot is a loss of time - if on a copy/paste excel and others can understand the data from the label and plot using reasonable defaults without many hints, certainly if columns are identified as datetime, labels etc. there could be a tool to use such hints and make a decent graph (to me, decent means giving a global understanding - sure you can tweak it to look good if you are preparing a report, but a lot of time is spent graphing thinks to figure things out and many graphs go to the trash in the process) My dream is to do my select queries in psql and direct the output to that tool, never leaving psql - so it could be for example something that would be triggered on a new table creation matching a specific name like xx_, then it would simply require prefixing "select" by "create table xx_abc as ". The best way I've found is to save the output to a CSV and pass it to other tools, but there are never quite user friendly and usually can't pick reasonable defaults. There is an OSX psql frontend I tried after it was recommended here on HN (http://inductionapp.com/ http://inductionapp.com/) but it was not that helpful in day to day operations. Yet it seemed to be on the same problem - see this picture https://s3.amazonaws.com/induction/induction-visualize.png https://s3.amazonaws.com/induction/induction-visualize.png
- atsaloli 14y ago
  Here is an example of feeding query output into gnuplot without leaving psql: # psql -U postgres psql (8.4.15) Type "help" for help. postgres=# \t Showing only tuples. postgres=# \a Output format is unaligned. postgres=# \f ' ' Field separator is " ". postgres=# select * from example; 1 1 2 2 3 3 4 4 postgres=# \o | /usr/bin/gnuplot postgres=# select 'set title "My Graph"; set terminal dumb 78 24; set key off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ; select * from example; postgres=# \o My Graph Time 4 ++----------+----------+-----------+----------+-----------+---------** + + + + + + **** + | **** | 3.5 ++ **** ++ | **** | | **** | 3 ++ *** ++ | **** | | **** | 2.5 ++ **** ++ | **** | | **** | 2 ++ *** ++ | **** | | **** | 1.5 ++ **** ++ | **** | + **** + + + + + + 1 **----------+----------+-----------+----------+-----------+---------++ 1 1.5 2 2.5 3 3.5 4 Servers postgres=#
thisisblurry 14y ago
Matt Thompson (https://github.com/mattt https://github.com/mattt), also from Heroku created Induction (http://inductionapp.com/ http://inductionapp.com/) to handle this sort of database administration around a year ago, but it seems like he's become pretty busy with other projects at the moment. I hope that he's able to pick up progress on it again as it really had a lot of promise behind it.
djthorpe 14y ago
I'm trying to build a mac GUI server app at the moment for postgresql for a standalone server, like Postgres.app. I should be finished in a couple of months or so and then I will write a GUI client app for Mac and iOS. If you're interested, you can download the source here: https://github.com/djthorpe/postgresql-kit https://github.com/djthorpe/postgresql-kit
sehrope 14y ago
We created JackDB[1], a database development client that works in the browser, to solve exactly this problem. We're making a lot of progress with it, including a lower price offering and launching a free tier soon. Check it out, or shoot me an email if you're interested. [1]: http://www.jackdb.com/ http://www.jackdb.com/
papsosouid 14y ago
Every time this topic comes up, people discuss tools they use which all seem to be nothing more than a graphical version of psql. As in, they let you see a list of tables, then see the columns of that table, run queries, etc. But none of that is actually making anything better or easier, its just a different client with the same features. Does anyone know of a tool that has a good visual DB design interface? That is what I am missing (not just when I work with postgresql, but period). I want to be able to easily and visually see the relationships between tables, not just the tables themselves, or one table with a list of its relationships. Something like this: http://ondras.zarovi.cz/sql/demo/ http://ondras.zarovi.cz/sql/demo/ but not web based and actually supporting all of postgresql?
- simcop2387 14y ago
  As much as it pains me to say this, that's one of the things that MS Access did fairly well. It might actually be possible to do that through ODBC with it actually.
- kochb 14y ago
  We've used MySQL Workbench to visually design our Postgres schema. We then export it into MySQL database, generate a migration based on that, and then apply the migration to a Postgres database. Before we used migrations, we instead had a shell script with a series of sed commands to map the MySQL SQL output to SQL that will work in Postgres. So, there are options, but I'd very much like a visual DB designer for Postgres as well.
- SkyMarshal 14y ago
  Not sure about other db's, but there are several pages on the PG wiki about gui tools: http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...
- defrost 14y ago
  There's a bit of a killer tool that's relatively well known in geospatial data circles (and has been kicking about for a few decades) called FME from Safe Software [1] that I've never seen referenced in the non spatial database circles. It's got some of the best table visualisation and table/tool/translation/QC intergration visual design tools I've seen. It's essentially built with the goal of starting with multiple table sources in various ASCII / <some>SQLDB format and displaying all tables, building filter pipes to merge and translate data on the fly and produce single or multiple coherent databases and table sets (or even more ascii tables) as output. It has it's quirks but it's a solid bit of kit ( I used it some years back to read in and unify data from several million leases (geospatial boundaries and related metadata) from multiple sources (Australian, Canadian, South African, etc. land departments) - from whoa to go was about four days, once running it chewed through the data on par with normal copy speeds (eg: it imported cleaned & filtered data in a time ballpark to just copying the data from A->B). I'm not affiliated, but on the basis of that job, yeah, I'll spruik it. [1] http://www.safe.com/ http://www.safe.com/ [2] http://www.safe.com/fme/fme-technology/fme-desktop/overview/ http://www.safe.com/fme/fme-technology/fme-desktop/overview/ ( See desktop demo video from overview section )
akurilin 14y ago
Are there any good guides out there for starting out Postgres 9.x+ administration, including proper safe deployment on a VPS, maintenance and all sorts of good practices as far as tools are concerned?
- atsaloli 14y ago
  I'd recommend "The Accidental DBA" (or, Administering PostgreSQL When It's Not Your Job). You can find it here: http://www.pgexperts.com/presentations.html http://www.pgexperts.com/presentations.html
  - akurilin 14y ago
    The Accidental DBA sounds like a really well fitting title for my request. Thanks! :)
    - atsaloli 14y ago
      You are most welcome!
- sickpig 14y ago
  PostgreSQL 9.0 High Performance, Gregory Smith Postgresql 9 Administration Cookbook, Simon Riggs, Hannu Krosing
  - akurilin 14y ago
    Thank you!
- deleted 14y ago
  [deleted]
EEGuy 14y ago
For ad-hoc, read-only query building on a Windows platform, I get a lot of mileage out of this quaint, non-CLI "toolchain": (1) pgAdminIII's query tool (not the tedious query builder, yes the bare SQL ("pencil" button) query tool, a plain text editor and SQL runtime (2) An editor able to both allow open files to be changed externally, and notify me that that's happened (no file close/reopen; can leave one file open for many round trips here). Also, have it make whitespace characters visible so one can see TAB characters. EditPlus does these job for me. (3) A spreadsheet program open to a blank, unnamed, unsaved sheet. You guessed it, I'm talking Excel. Here's the workflow: (A) Run the next try of the query-build-in-progress in the query tool, sending its output to the file "simultaneously open" on EditPlus. Include headers, and use a column separator that's unlikely to occur in data, e.g. the pipe symbol, '|'. Almost all keystrokes. (B) Switch to EditPlus. It then politely notices the file's been changed, assent to its doing a file reload (no close/re-open in the UI, but of course that's what it does). Two keystrokes. (C) Globally change | to \t, but don't bother to save the file. Just select all and copy to clipboard. A few keystrokes. (D) Switch to spreadsheet, paste. Two keystrokes. Here is where using the TAB character as a column separator works its good magic; all the headings and all the query result drop into properly aligned cells in the spreadsheet. Analyze the results in the spreadsheet, maybe highlight some color on problematic rows, columns or cells. Go back to the SQL editor and re-run the whole chain. No file naming (except once on startup), no import wizard (ugh!), no open/close, no CSV misinterpretation. All stock software. Various ways of icing this particular workflow-cake: * After pasting into the sheet, highlight the columns that Excel typed as numeric when you know they're character values that happen to be all digits. Use 'Format Cells' to change the 'Number' (data type!) from 'General' to 'Text'. Nothing will appear to change, but here's the magic: Highlight all the cells and delete their content (delete key). Now paste from clipboard again. This type the character data hasn't lost its leading zeroes, and it's properly left-justified. * Start with a SELECT ... then once the result is in Excel, delete the colums it turns out you don't want... then hightlight those headings, copy to clipboard, paste to editor, reformat as comma-separated, copy and paste that in place of the in the original select. * Use underscores in column names instead of camel-case, e.g. row_id instead of rowId (camel-case doesn't work in PG unless column identifiers are quoted, not worth the pain). Once in Excel, highlight all the column names, repalce underscore with blank, set the Format to Wrap. Now the column names form a distinctive taller-than-the-data-rows, easy-to-read. I've come to think of (and use) the clipboard as a manual, stepwise imitation of the character stream native to shell scripting of *nix CLI tools. Not a true 'toolchain' in that one has to manually pump everything through the clipboard, but but still effective and quick enough for fast, "filenameless" turnaround on query development, and very little mousework. Most of all, it gives me the very significant power of a spreadsheet for query error analysis.
pyxy 14y ago
`win psql` in Acme window is my choice: you get per-session history for free, easy copy-paste (with a real 3-button mouse of course) and even multiline SQL queries are very comfortable.
- pyxy 14y ago
  forgot to mention some useful psql options for comfortable work within Acme: `win psql -xn -vPROMPT2=` setting PROMPT2 to null string gifts you with ability to easily copy-paste SQL expressions `-n` disables readline lib because we don't need it in Acme

149 points by craigkerstiens 14y ago

kochb 14y ago

Just found TeamPostgreSQL (http://www.teampostgresql.com/ http://www.teampostgresql.com/) earlier today, by far the best I've ever worked with. Not that the other tools have set a very high bar. pgAdmin crashes constantly, and you can't sort columns by clicking on them. Navicat doesn't run properly on Linux, you have to mess around with wine. phpPgAdmin has a single DB host is hard coded into a config file and just feels antiquated. One major drawback of TeamPostgreSQL so far: it doesn't support SSL connections.

staz 14y ago
It doesn't support SSL connexion to the database or it doesn't support HTTPS ? (And does it work if you put it behind a proxy?)

jaytaylor 14y ago

I've frequently given the same advice to my co-workers. The GUI's for Postgres are all lackluster, and learning to use psql with \d seems like the best way to go. It takes a little time to get used to, but once acclamated it blends into the background and works great.

Tashtego 14y ago

Great article but I can't get his advice for using Sublime Text as the editor to work. \e will open a new file, but saving/quitting doesn't ever run the query.

stevvooe 14y ago
'subl -w' is the correct incantation.
- xentronium 14y ago
  Last time I checked, it didn't work in linux iff there were other sublime windows open. See http://stackoverflow.com/questions/14598261/making-sublime-text-2-command-on-linux-behave-as-it-does-on-macos-x http://stackoverflow.com/questions/14598261/making-sublime-t...
- Tashtego 14y ago
  Thanks- that works!

msluyter 14y ago

I've been using Squirrel and it seems ok; fairly decent most of the time, actually. (Anyone else have any thoughts on Squirrel?) I've never used psql though. I'll look into it.

gburt 14y ago
Squirrel, like all Java software I have ever used, has this bad habit of getting in to an inconsistent state where it needs to be restarted to use.

ScotterC 14y ago

Can't wait till Sequel Pro supports Postgres. Was really excited to see them publicly start down the path of implementing it last September. http://stuconnolly.com/blog/sequel-pro-postgresql-support/ http://stuconnolly.com/blog/sequel-pro-postgresql-support/

johne20 14y ago
I agree. I honestly think this is the biggest thing slowing adoption of psql. It is the one thing I miss from mysql.

dinkumthinkum 14y ago

That's cute but, let's be honest, viewing query results for anything but trivial schemas or amounts of data with psql is somewhat close to impractical.

Roboprog 14y ago
Dump the result set as a table into a file and refresh "the file" in a browser? Having 2 windows open is about the same as having 2 panels in one of the DB "Monties". http://catb.org/jargon/html/M/monty.html http://catb.org/jargon/html/M/monty.html
- Roboprog 14y ago
  That said, the sql*pro CLI that Oracle provides for their DB SUCKS (!!!) rotten eggs, and gives people the impression that a CLI is useless for database work.
  - sehrope 14y ago
    Yes the CLI for sql*plus does suck. When I am using it (pretty rare these days) I use rlwrap to make it suck less. Command history is pretty convenient too. Here's my ~/bin/sqlplus shell script pointing to the Oracle instant client install: #!/bin/sh OIC_DIR=$HOME/opt/oracle-instant-client export LD_LIBRARY_PATH="${OIC_DIR}:$LD_LIBRARY_PATH" export TNS_ADMIN="${OIC_DIR}" rlwrap "${OIC_DIR}/sqlplus" "$@" Bonus: You can use a tnsnames.ora file with the instant client if you put it in the instant client directory and export the environment variable in the script above.
    - Roboprog 14y ago
      That's right, it's been a while. "pro" was the C preprocessor, "plus" was the gawdhawful CLI. Thanks for the tip on rlwrap, in case I have to use that thing again. I'm assuming it provides GNU readline support on top of the base tool.
      - 14y ago

drewda 14y ago

Navicat is a decent way to deal with both Postgres and MySQL (among many other database engines) with the same interface. All the different ways Navicat packages its products are unnecessarily confusing. Premium Essentials ($20) is almost certainly what you want: http://www.navicat.com/en/products/navicat_essentials/essentials_overview.html http://www.navicat.com/en/products/navicat_essentials/essent...

SkyMarshal 14y ago
Just downloaded their trial edition a few days ago for an upcoming project but haven't started playing with it yet. Glad to hear good things about it.

alternize 14y ago

i'm pretty happy with ems' sql manager - while not free, it has everything i ever needed from a postgres gui. working with psql to query large data sets seems a bit tedious to me - i prefer a multiwindow gui application with a decent tabular view that i can browse through. i heavily use psql for maintenance stuff tho.

olefoo 14y ago

I'm partial to SQL-mode in emacs which allows you to start psql in a comint style buffer, send queries from an edit buffer and capture them to output buffers; it used to be a pain to set up but these days it mostly just works. And by accident or design, there is almost no interference between the psql commands and the editor.

Roboprog 14y ago

But, but, but! If I use the CLI instead of a GUI, people might start asking me to put what I am doing into a file and save it in revision control. They might even ask me to make a function (procedure) out of some of it so it can be extended or reused. Then where would we be -- how can I claim that DBA-ing is magic? :-)

Roboprog 14y ago
Funny: this got upvoted, then immediately downvoted. Come on, tell the truth: how many of you have had The Great and Powerful Oz for a DBA? (there's a wonderful BOFH episode about the BOFH vs a DBA - read when in a foul mood some time for a chuckle)
papsosouid 14y ago
I find your characterization very puzzling. It is the DBAs I've worked with who want to use revision control, who want to use correct database schemas, who want data integrity, who want to use stored procedures. They are the ones fighting against developers who never bothered to learn how to use a relational database and push the mysql/php style of "just make your app the database and the database is just a file full of inconsistent data, don't do joins, don't use view, don't use stored procedures or functions, don't use triggers, etc."
- Roboprog 14y ago
  Either you have been lucky, or my coworkers and I have been unlucky. YMMV. Fortunately, most all the DBAs I've worked with do in fact value data integrity, but, don't seem to care about other development practices like automated DB version migrations or reducing repetition.

guylhem 14y ago

I do the same, but I have one thing I miss - an easy way to send the output out for graphs. Tweaking and playing with gnuplot is a loss of time - if on a copy/paste excel and others can understand the data from the label and plot using reasonable defaults without many hints, certainly if columns are identified as datetime, labels etc. there could be a tool to use such hints and make a decent graph (to me, decent means giving a global understanding - sure you can tweak it to look good if you are preparing a report, but a lot of time is spent graphing thinks to figure things out and many graphs go to the trash in the process) My dream is to do my select queries in psql and direct the output to that tool, never leaving psql - so it could be for example something that would be triggered on a new table creation matching a specific name like xx_, then it would simply require prefixing "select" by "create table xx_abc as ". The best way I've found is to save the output to a CSV and pass it to other tools, but there are never quite user friendly and usually can't pick reasonable defaults. There is an OSX psql frontend I tried after it was recommended here on HN (http://inductionapp.com/ http://inductionapp.com/) but it was not that helpful in day to day operations. Yet it seemed to be on the same problem - see this picture https://s3.amazonaws.com/induction/induction-visualize.png https://s3.amazonaws.com/induction/induction-visualize.png

atsaloli 14y ago
Here is an example of feeding query output into gnuplot without leaving psql: # psql -U postgres psql (8.4.15) Type "help" for help. postgres=# \t Showing only tuples. postgres=# \a Output format is unaligned. postgres=# \f ' ' Field separator is " ". postgres=# select * from example; 1 1 2 2 3 3 4 4 postgres=# \o | /usr/bin/gnuplot postgres=# select 'set title "My Graph"; set terminal dumb 78 24; set key off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ; select * from example; postgres=# \o My Graph Time 4 ++----------+----------+-----------+----------+-----------+---------** + + + + + + **** + | **** | 3.5 ++ **** ++ | **** | | **** | 3 ++ *** ++ | **** | | **** | 2.5 ++ **** ++ | **** | | **** | 2 ++ *** ++ | **** | | **** | 1.5 ++ **** ++ | **** | + **** + + + + + + 1 **----------+----------+-----------+----------+-----------+---------++ 1 1.5 2 2.5 3 3.5 4 Servers postgres=#

thisisblurry 14y ago

Matt Thompson (https://github.com/mattt https://github.com/mattt), also from Heroku created Induction (http://inductionapp.com/ http://inductionapp.com/) to handle this sort of database administration around a year ago, but it seems like he's become pretty busy with other projects at the moment. I hope that he's able to pick up progress on it again as it really had a lot of promise behind it.

djthorpe 14y ago

I'm trying to build a mac GUI server app at the moment for postgresql for a standalone server, like Postgres.app. I should be finished in a couple of months or so and then I will write a GUI client app for Mac and iOS. If you're interested, you can download the source here: https://github.com/djthorpe/postgresql-kit https://github.com/djthorpe/postgresql-kit

sehrope 14y ago

We created JackDB[1], a database development client that works in the browser, to solve exactly this problem. We're making a lot of progress with it, including a lower price offering and launching a free tier soon. Check it out, or shoot me an email if you're interested. [1]: http://www.jackdb.com/ http://www.jackdb.com/

papsosouid 14y ago

Every time this topic comes up, people discuss tools they use which all seem to be nothing more than a graphical version of psql. As in, they let you see a list of tables, then see the columns of that table, run queries, etc. But none of that is actually making anything better or easier, its just a different client with the same features. Does anyone know of a tool that has a good visual DB design interface? That is what I am missing (not just when I work with postgresql, but period). I want to be able to easily and visually see the relationships between tables, not just the tables themselves, or one table with a list of its relationships. Something like this: http://ondras.zarovi.cz/sql/demo/ http://ondras.zarovi.cz/sql/demo/ but not web based and actually supporting all of postgresql?

simcop2387 14y ago
As much as it pains me to say this, that's one of the things that MS Access did fairly well. It might actually be possible to do that through ODBC with it actually.
kochb 14y ago
We've used MySQL Workbench to visually design our Postgres schema. We then export it into MySQL database, generate a migration based on that, and then apply the migration to a Postgres database. Before we used migrations, we instead had a shell script with a series of sed commands to map the MySQL SQL output to SQL that will work in Postgres. So, there are options, but I'd very much like a visual DB designer for Postgres as well.
SkyMarshal 14y ago
Not sure about other db's, but there are several pages on the PG wiki about gui tools: http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...
defrost 14y ago
There's a bit of a killer tool that's relatively well known in geospatial data circles (and has been kicking about for a few decades) called FME from Safe Software [1] that I've never seen referenced in the non spatial database circles. It's got some of the best table visualisation and table/tool/translation/QC intergration visual design tools I've seen. It's essentially built with the goal of starting with multiple table sources in various ASCII / <some>SQLDB format and displaying all tables, building filter pipes to merge and translate data on the fly and produce single or multiple coherent databases and table sets (or even more ascii tables) as output. It has it's quirks but it's a solid bit of kit ( I used it some years back to read in and unify data from several million leases (geospatial boundaries and related metadata) from multiple sources (Australian, Canadian, South African, etc. land departments) - from whoa to go was about four days, once running it chewed through the data on par with normal copy speeds (eg: it imported cleaned & filtered data in a time ballpark to just copying the data from A->B). I'm not affiliated, but on the basis of that job, yeah, I'll spruik it. [1] http://www.safe.com/ http://www.safe.com/ [2] http://www.safe.com/fme/fme-technology/fme-desktop/overview/ http://www.safe.com/fme/fme-technology/fme-desktop/overview/ ( See desktop demo video from overview section )

akurilin 14y ago

Are there any good guides out there for starting out Postgres 9.x+ administration, including proper safe deployment on a VPS, maintenance and all sorts of good practices as far as tools are concerned?

atsaloli 14y ago
I'd recommend "The Accidental DBA" (or, Administering PostgreSQL When It's Not Your Job). You can find it here: http://www.pgexperts.com/presentations.html http://www.pgexperts.com/presentations.html
- akurilin 14y ago
  The Accidental DBA sounds like a really well fitting title for my request. Thanks! :)
  - atsaloli 14y ago
    You are most welcome!
sickpig 14y ago
PostgreSQL 9.0 High Performance, Gregory Smith Postgresql 9 Administration Cookbook, Simon Riggs, Hannu Krosing
- akurilin 14y ago
  Thank you!
deleted 14y ago
[deleted]

EEGuy 14y ago

For ad-hoc, read-only query building on a Windows platform, I get a lot of mileage out of this quaint, non-CLI "toolchain": (1) pgAdminIII's query tool (not the tedious query builder, yes the bare SQL ("pencil" button) query tool, a plain text editor and SQL runtime (2) An editor able to both allow open files to be changed externally, and notify me that that's happened (no file close/reopen; can leave one file open for many round trips here). Also, have it make whitespace characters visible so one can see TAB characters. EditPlus does these job for me. (3) A spreadsheet program open to a blank, unnamed, unsaved sheet. You guessed it, I'm talking Excel. Here's the workflow: (A) Run the next try of the query-build-in-progress in the query tool, sending its output to the file "simultaneously open" on EditPlus. Include headers, and use a column separator that's unlikely to occur in data, e.g. the pipe symbol, '|'. Almost all keystrokes. (B) Switch to EditPlus. It then politely notices the file's been changed, assent to its doing a file reload (no close/re-open in the UI, but of course that's what it does). Two keystrokes. (C) Globally change | to \t, but don't bother to save the file. Just select all and copy to clipboard. A few keystrokes. (D) Switch to spreadsheet, paste. Two keystrokes. Here is where using the TAB character as a column separator works its good magic; all the headings and all the query result drop into properly aligned cells in the spreadsheet. Analyze the results in the spreadsheet, maybe highlight some color on problematic rows, columns or cells. Go back to the SQL editor and re-run the whole chain. No file naming (except once on startup), no import wizard (ugh!), no open/close, no CSV misinterpretation. All stock software. Various ways of icing this particular workflow-cake: * After pasting into the sheet, highlight the columns that Excel typed as numeric when you know they're character values that happen to be all digits. Use 'Format Cells' to change the 'Number' (data type!) from 'General' to 'Text'. Nothing will appear to change, but here's the magic: Highlight all the cells and delete their content (delete key). Now paste from clipboard again. This type the character data hasn't lost its leading zeroes, and it's properly left-justified. * Start with a SELECT ... then once the result is in Excel, delete the colums it turns out you don't want... then hightlight those headings, copy to clipboard, paste to editor, reformat as comma-separated, copy and paste that in place of the in the original select. * Use underscores in column names instead of camel-case, e.g. row_id instead of rowId (camel-case doesn't work in PG unless column identifiers are quoted, not worth the pain). Once in Excel, highlight all the column names, repalce underscore with blank, set the Format to Wrap. Now the column names form a distinctive taller-than-the-data-rows, easy-to-read. I've come to think of (and use) the clipboard as a manual, stepwise imitation of the character stream native to shell scripting of *nix CLI tools. Not a true 'toolchain' in that one has to manually pump everything through the clipboard, but but still effective and quick enough for fast, "filenameless" turnaround on query development, and very little mousework. Most of all, it gives me the very significant power of a spreadsheet for query error analysis.

pyxy 14y ago

`win psql` in Acme window is my choice: you get per-session history for free, easy copy-paste (with a real 3-button mouse of course) and even multiline SQL queries are very comfortable.

pyxy 14y ago
forgot to mention some useful psql options for comfortable work within Acme: `win psql -xn -vPROMPT2=` setting PROMPT2 to null string gifts you with ability to easily copy-paste SQL expressions `-n` disables readline lib because we don't need it in Acme