Questions tagged [psql]
psql is official postgresql client
49
questions
0
votes
0
answers
49
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 ...
- 121
0
votes
1
answer
3k
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 ...
- 103
10
votes
1
answer
10k
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 ...
- 5,388
3
votes
1
answer
3k
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 ...
- 133
0
votes
1
answer
358
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/...
- 27
0
votes
1
answer
270
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+
...
- 1
0
votes
1
answer
310
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 ...
- 101
0
votes
0
answers
77
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-...
- 1
0
votes
1
answer
1k
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 ...
1
vote
0
answers
677
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 ...
- 11
0
votes
1
answer
573
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 "...
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 ($...
- 101
1
vote
0
answers
265
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('...
- 255
1
vote
2
answers
13k
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....
- 2,068
-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) > ...
- 101
3
votes
4
answers
23k
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/...
- 590
19
votes
2
answers
45k
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 ...
- 511
1
vote
0
answers
165
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.
...
- 201
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-...
- 353
-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: ...
- 203
5
votes
1
answer
920
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 ...
- 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)
...
- 653
2
votes
2
answers
280
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. ...
- 911
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 ...
- 667
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 ...
- 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: "...
- 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 ...
- 119
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 ...
4
votes
1
answer
5k
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
...
- 615
25
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, ...
- 361
-1
votes
1
answer
329
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 ...
- 2,783
4
votes
2
answers
696
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 ...
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 ...
- 149
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 ...
- 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 ...
- 200
1
vote
1
answer
92
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 ...
- 295
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 "...
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 ...
- 567
0
votes
1
answer
596
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 ...
- 567
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, ...
- 273
30
votes
3
answers
42k
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,135
2
votes
2
answers
991
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 ...
- 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, ...
- 121
104
votes
6
answers
67k
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 ...
- 1,163
0
votes
1
answer
597
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 ...
- 335
8
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 ...
- 3,200
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 ...
- 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 ...
RiZe
36
votes
2
answers
15k
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 ...
- 963