psql is official postgresql client

learn more… | top users | synonyms

0
votes
0answers
30 views

.pgpass other system user as in file

i have a working script with a working .pgpass file. Now i want to run the script as root with the same(copy) .pgpass file. I copied the file in the $HOME directory from root. And changed permission ...
1
vote
0answers
18 views

psql: How to include comments in \d output

In PostgreSQL it is possible to add comments to table columns. These will appear in the psql output from the "extended describe" \d+ command, however they will not appear in the standard \d output. ...
0
votes
2answers
43 views

PostgreSQL password issue with psql

I'm trying to setup PostgreSQL onto a CentOS (that I connect through SSH). I need to unzip a package (so not downloading through yum or rpm): ...
-3
votes
1answer
260 views

Psql uses .pgpass file however pg_dump does not, and it also does not connect using the correct password [closed]

Using the following command: % pg_dump -h postgres.cs.host.ca -p 5433 -n _cscf_subscription -Fc -f ~/old.dump odyssey_prod Password: pg_dump: [archiver (db)] connection to database "cs-rsub" failed: ...
3
votes
1answer
122 views

`psql` expanded mode equivalency for `mysql`

Am frustated by things like: 0:33:1407402356:root@ahost:~# echo 'use wordpress_3_6_1; select * from wp_posts;' | mysql -u mysqluser -pmysqlpasswdord | wc -l -L 42 40585 SQL query results being ...
0
votes
1answer
96 views

Which package to upgrade psql client?

I have updated my Postgres install from 8.4 to 9.3. Now when I log onto the server with the initial command sudo -u postgres psql template1 I get the following error psql (8.4.18, server ...
0
votes
2answers
49 views

postgres shell keeps hiding the information

When using psql, \d <table> or select * from XXXX, after I exit and go back to shell, the information is not there and I need it to do the next command all the time. This is very very annoying. ...
2
votes
1answer
160 views

Monit “is alive” check of PostgreSQL tends to fail during a run of pg_dump, is there a better way?

I use pg_dump for my primary backup, once every three hours. I also use monit. When monit checks if PostgreSQL is alive during the pg_dump run, it sometimes times out, and restarts postgres. This ...
0
votes
1answer
3k views

pg_dump: [archiver (db)] connection to database failed: FATAL: Peer authentication failed for user “postgres”

I'm trying to backup my Postgres DB to another server but I keep getting denied access. my pg_hba.conf file looks like this: # DO NOT DISABLE! # If you change this first entry you will need to make ...
-1
votes
1answer
351 views

Unable to login in phpPgAdmin

I am trying to login to phpPgAdmin from a remote client. I've created the user root, but when I login phpPgAdmin returns an error message saying "Unable to login" When I check the log it says: ...
-1
votes
2answers
512 views

Postgresql pgdump script on RHEL5: “failed: fe_sendauth: no password supplieddatabase ”

What I'm trying to accomplish is having a pg_dump script run as a cron job. Right now I'm just trying to get the script to run by invoking it myself at the command line. I've searched the postgresql ...
0
votes
2answers
209 views

pgsql can connect, pgadmin cannot

We want only 1 role owning 1 database, Domain-unix Socket, no password postgres@luciol-essai:~$ more /etc/postgresql/8.4/main/pg_hba.conf | grep jppstphp local essais_php jppstphp ...
0
votes
1answer
236 views

SSH tunnel to a postgres database [closed]

Currently I develop on a remote dev server. I SSH into the dev server (using SSH keys) as ssh -p 22222 user@devbox.com Then from the dev server, I connect to the database over the local network ...
3
votes
0answers
376 views

Postgres roles best practice implementation

Folks, I could use your help making my Postgres user access control design better and more aligned with best practices. I'm helping roll out a small production Postgres server but I am not a DB ...
-1
votes
1answer
83 views

I'm able to login via “psql” as “posgres” with any linux user - is this a security issue?

I used sudo su postgres and set up my database while logged in as the linux user postgres. After exiting to my normal shell, logged in as my non-root user, I can simply use psql -U postgres and then ...
3
votes
2answers
297 views

PSQL 64bit driver error

I have an Ubuntu 12.04 64bit server setup under Hyper-V. I have installed Pervasive 64bit SQL drivers so that a stock-updater script can run daily (Updates external MySQL database from another local ...
2
votes
1answer
330 views

psql editor setting on Ubuntu

The situation is the following. This is an Ubuntu box: Linux ns3mx3 2.6.32-41-server #89-Ubuntu SMP Fri Apr 27 22:33:31 UTC 2012 x86_64 GNU/Linux Which means that when I first issue \e in psql, ...
1
vote
1answer
429 views

postgres server works on LAN but not WAN (connection refused)

I'm trying to set up a postgres server, but I cannot access it through the Internet. It works fine on my LAN. Server is just a desktop computer using Ubuntu 12.04 desktop. disclaimer: I am not ...
2
votes
1answer
184 views

psql asks for password despite configuring trust authentication from localhost

In /var/lib/pgsql/9.1/data/pg_hba.conf, I have the following: # TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 ...
1
vote
1answer
80 views

“distinct” query returning me multiple records [closed]

On executing the following query I am getting multiple records as result with each having id=10. If I am using distinct keyword, then I should get just one record. What could be the cause? "select ...
0
votes
1answer
1k views

Cannot access psql after installing postgres

I installed postgresql via homebrew on my 10.7.3. The install seemed successful and I was able to start up the server. However when I try to access psql I get the following error: psql: FATAL: role ...
0
votes
2answers
45 views

Database not browsable during COPY on PostgreSQL

When using COPY to restore a CSV file, phppgadmin shows that the real database size(4GB) but when I choose to browse the table it shows Estimated row count to 0. I did a VACUUM ANALYZE. simple SELECT ...
0
votes
1answer
173 views

ssl handshake failure with COPY on PostgreSQL

I'm restoring a CSV file to database with COPY command. The csv file is bziped. I use this command: bunzip2 -c -d online-20110923000001.csv.bz2 | psql -U user -h 127.0.0.1 -d testdb -c "COPY data ...
0
votes
1answer
577 views

Using psql with two postgresql installations on Windows 7

I have installed two versions of Postgresql, 8.4 and 9.1. When I try to run psql it only picks up the previous psql version. For example, when I run the following line to connect to the database, ...
4
votes
3answers
4k views

How can I show the content of a trigger with psql?

I know I can list the triggers with \dft. But how can I see one concrete trigger? I want to know details like on which events the trigger is executed, which function is executed and so on.
1
vote
2answers
336 views

ssh connection interrupted while interactive psql running command--will command finish? Can I reconnect?

I was using interactive psql to run a reindex on one of our databases. Unfortunately, the ssh connection dropped while the reindex was still running and I had foolishly not put psql inside a screen ...
1
vote
1answer
293 views

PSQL error with restarting postgres in SSL

I set up server.crt, root.crt and servery.key in postgresql (using http://howtoforge.com/postgresql-ssl-certificates for instructions), set ssl = on and restarted the service successfully. However, ...
7
votes
2answers
2k views

Turn off pager for psql's interactive output

We switched from PostgreSQL 8.3 to 9.0. Perhaps it's a new feature or perhaps just a configuration change, but now when output from commands (like, \d tablename) exceeds visible vertical space, psql ...
0
votes
1answer
416 views

Problem with diacritics on psql 9.0 (PostgreSQL)

I have two instances of PostgreSQL installed on my server: 8.3 and 9.0. There seams to be some problem with Polish diacritic characters (like ółęąśżźć) on postgresql 9.0 client - psql. When I connect ...
0
votes
2answers
2k views

psql empty database before restore

I want to restore a database of postgreSQL, but before do it i need to empty the restore target database. Is there an option of psql to do it ? Now i use a command line as the following : psql -U ...
1
vote
3answers
666 views

Why does psql attempt to connect with a different username when run from a Perl script

I have a bunch of Perl scripts that have recently stopped working. I have narrowed the problem down to this: when I connect to a postgresql database from a perl script, it attempts to use the wrong ...
0
votes
4answers
2k views

PostgreSQL 8.4 won't start after blackout

I have problem with starting PostgreSQL 8.4 on Ubuntu 9.10 Server after blackout. When I try to connect to the database it says: psql: server closed the connection unexpectedly This probably means ...
7
votes
2answers
2k views

Postgres: Non zero exit code when executing a sql file?

I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f). e.g. psql -c "create table foo (bar integer)" psql -f foobar.sql One ...