Connecting to a Redshift cluster from pgAdmin

Question

UPDATE: also asked on the PgAdmin-support mailing list here.

---

So I have an AWS Redshift cluster up and running, and I'm able to connect to it from the command line with

$ psql -h host -d database -p port -U username

I want to connect to the cluster through pgAdmin III, but after I connect to the cluster, I get an error that states:

Error: Must be superuser to view default_tablespace

Note, the fields I entered were: Name, Host, Port, Maintenance DB, Username, and Password, all of which were entered the same as with the psql command.

Now, if I connect via psql, and I check:

$ \tu

This user [the master user granted by AWS] is, in fact, a superuser. So are there any suggestions for what might be the cause of this error?

As a follow up question, when I connect to other remote dbs, I don't get this error even though I am not a superuser, so what is happening here? What about Redshift causes this error to happen when connecting via pgAdmin?

Answer 1

PGAdmin 1.6.3 (from March 2007!) is working fine for me (on OS X) with Redshift.

This makes sense I guess, given that Redshift was forked from Postgres 8.0.

NOTE: by 'fine' I mean the things that I want from a GUI work OK. There are still a few quirks and error messages to be dismissed.

Answer 2

If all you need is a GUI way to edit a complex SQL and you don't want to copy-paste every time you make a change you can use an editing shortcut

pguser=> \e

This will open the editor of your choice (saved on your machine as $EDITOR)

To overwrite it just for the sessions's keep (if your default is vim but want to use gedit) run this:

$ EDITOR=gedit psql -h host -d database -p port -U username

Edit your SQL using \e then save and quit. It'll run your code. Then just open \e again and it'll populate your editor with previous query.

Enjoy!

Answer 3

Try SQLWorkbench. I haven't tried pgadmin, but SqlWorkbench and Postgresql jdbc connector works for me perfectly.