Questions tagged [psql]

psql is official postgresql client

Filter by
Sorted by
Tagged with
0 votes
0 answers
67 views

psql shell over ssh via bash script doesn't get user input

I'm opening a ssh connection and then go into a psql shell. This works, I get the prompt. I can also input commands like "\dt". But I don't get any output. As I can get output in general, I ...
steros's user avatar
  • 121
0 votes
1 answer
4k views

Postgres Role required to grant CONNECT on a specific database

I am trying to GRANT CONNECT on a DATABASE to a user as a schema owner in that database. The schema owner is not a superuser. Do I need to be the owner of a database to execute GRANT CONNECT ON ...
DerpDerp's user avatar
  • 103
10 votes
1 answer
12k views

Set connection timeout using psql command line interface

Is there a way to set the connection timeout using psql cli? How can I set a connection timeout to something low like 3 seconds using the following: PGPASSWORD=passwordhere psql -h 10.0.0.144 -U ...
Justin's user avatar
  • 5,438
3 votes
1 answer
4k views

Double port forwarding kubernetes + docker

Summary: I have a docker container which is running kubectl port-forward, forwarding the port (5432) of a postgres service running as a k8s service to a local port (2223). In the Dockerfile, I have ...
Chayan Ghosh's user avatar
0 votes
1 answer
410 views

Security issue with pg_hba.conf

I have a PHP script on my server that needs access to a database user to work properly . The person who programmed that PHP script has told to add the following to pg_hba.conf : host all all 127.0.0.1/...
master lfc6's user avatar
0 votes
1 answer
363 views

Postgres Table size & record counts

I want to understand the below result behavior for finding a size for a table with x MB while try to count record in this table but no records found in postgres database db_server=# \dt+ ...
Moustafa's user avatar
0 votes
1 answer
333 views

How do I permanently upgrade psql client version in Google Cloud Shell?

From this answer I need to upgrade the psql in the Google Cloud Shell. But any upgrade I do there it shows a warning like this: Note that your Cloud Shell machine is ephemeral and no system-wide ...
Zenko's user avatar
  • 101
0 votes
0 answers
95 views

I can't get the postgres installation correctly

I have spent quite a bit of time to install postgresql 11 on the high side production, but I just can't quite get it installation and configured correctly after I yum install rpm -i postgresl11-server-...
Pepe's user avatar
  • 1
0 votes
1 answer
2k views

specifying server port (not postgres port) for pg_dump / psql

I have a server named as servername.mydomain.com. I can do ssh to this server using a port number X using: ssh [email protected] -p X From this server I can run: psql -h localhost ...
Jose Cabrera Zuniga's user avatar
1 vote
0 answers
731 views

Why are psql commands in my script suddenly being killed by jenkins / hudson?

I have an existing jenkins job that kicks off a shell script to copy my prod environment into qa. We added a lot of data to prod (gzip dump went from 2gig to 15gig) and all of the sudden my jenkins ...
Ted's user avatar
  • 11
0 votes
1 answer
597 views

I think I am unable to relock my postgres psql account

When I was setting up postgresql on linux, I was following a tutorial that I think had me change the postgres account password and I did not know any better. Please forgive my ignorance. When I type "...
Humble-Data-22's user avatar
0 votes
2 answers
1k views

pg_dump fails as a cron job in one script but not another

I have two cron jobs on my AWS server (triggered via crontab -e) to invoke pg_dump on specific databases. The two scripts are identical with the exception of the target database they're backing up ($...
PGT's user avatar
  • 101
1 vote
0 answers
274 views

parallize postgres queries with bash and psql?

I'm trying to run a series of postgres functions/stored-procedures that operate on separate tables in parallel using bash + psql as follows: psql -d samples_20160612_0616 -c "select insert_function('...
monkut's user avatar
  • 255
1 vote
2 answers
14k views

Why doesn't "psql -U" work for me?

I'm just starting with Postgres. This is a fresh install of Postgres 9.5 on Ubuntu 14.04 LTS. I used the apt repo from: http://www.postgresql.org/download/linux/ubuntu/ The intro page http://www....
fadedbee's user avatar
  • 2,088
-1 votes
1 answer
84 views

Cronjob doesn't run. Similar cronjob does run. Issue with Env Vars??? Fedora involved. psql involved [duplicate]

I have cronjobs like so * * * * * /usr/bin/psql -U usename dbname -c "select usename, now(), pg_terminate_backend(pid) from pg_stat_activity where state like 'idle%' and (now() - query_start) > ...
artdv's user avatar
  • 101
3 votes
4 answers
24k views

Postgresql: restoring database from dump fails

I am using psql (9.3.9) on Ubuntu 14.04. I created dump of a database using command: pg_dump db1 > db1.backup I deleted the db and recreated it. Trying to restore it with psql -d db1 -f /tmp/...
Madoc Comadrin's user avatar
21 votes
2 answers
49k views

How to specify a client certificate to psql?

I have a Postgres server with a user dev which requires a client certificate to log in. I'm using the command psql "sslmode=require user=dev host=db.prod", which gives me psql: FATAL: connection ...
Jorn's user avatar
  • 541
1 vote
0 answers
176 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. ...
Daniel Fortunov's user avatar
0 votes
2 answers
4k 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): http://get.enterprisedb.com/postgresql/postgresql-9.3.4-3-...
kolistivra's user avatar
-3 votes
1 answer
4k 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: ...
ComputerLocus's user avatar
5 votes
1 answer
936 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 ...
178024's user avatar
  • 36
5 votes
2 answers
10k 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 9.3.5) ...
wobbily_col's user avatar
2 votes
2 answers
300 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. ...
Dean Hiller's user avatar
2 votes
1 answer
1k 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 ...
Terry G Lorber's user avatar
7 votes
1 answer
36k 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 ...
Drace's user avatar
  • 175
0 votes
1 answer
2k 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: "...
Alfons's user avatar
  • 11
0 votes
2 answers
3k 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 ...
Tim Sanders's user avatar
0 votes
3 answers
2k 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 ...
Jean-Pierre's user avatar
4 votes
1 answer
6k 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 [email protected] Then from the dev server, I connect to the database over the local network ...
hobbes3's user avatar
  • 635
26 votes
1 answer
9k 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 admin, ...
JP Beaudry's user avatar
-1 votes
1 answer
348 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 ...
orokusaki's user avatar
  • 2,863
4 votes
2 answers
700 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 ...
Alexander Holsgrove's user avatar
4 votes
1 answer
2k 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, I'm ...
András Váczi's user avatar
1 vote
1 answer
2k 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 ...
buck's user avatar
  • 111
3 votes
2 answers
5k 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 ...
Steve's user avatar
  • 200
1 vote
1 answer
93 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 ...
Gautam Somani's user avatar
1 vote
1 answer
5k 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 "...
Andrew Lauer Barinov's user avatar
0 votes
2 answers
58 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 ...
Majid Azimi's user avatar
0 votes
1 answer
610 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 ...
Majid Azimi's user avatar
1 vote
1 answer
1k 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, ...
celenius's user avatar
  • 273
30 votes
4 answers
43k 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.
Mnementh's user avatar
  • 1,135
2 votes
2 answers
1k 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 ...
eulerz's user avatar
  • 23
2 votes
1 answer
2k 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, ...
Angry Spartan's user avatar
107 votes
6 answers
72k 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 ...
Yuri Ushakov's user avatar
  • 1,193
0 votes
1 answer
605 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 ...
grzaks's user avatar
  • 335
9 votes
2 answers
20k 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 ...
aleroot's user avatar
  • 3,210
1 vote
3 answers
1k 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 ...
mojones's user avatar
  • 165
0 votes
4 answers
4k 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 ...
user avatar
36 votes
2 answers
16k 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 ...
Jin Kim's user avatar
  • 973