Java Solutions: psql

Showing posts with label psql. Show all posts

Thursday, October 6, 2016

postgresql alter table hangs

When the SQL, "Alter table Fruit add Column Location VarChar(30))", is executed at the psql terminal, it hangs.

It turns out that there is a lock on a table. The SQL below helps find all the locks in your database.

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Then use the SQL below to kill the blocking process.

select pg_terminate_backend(<blocking_pid>);

Reference:

Lock Monitoring

---------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Wednesday, September 28, 2016

[Solved] Use a different port or change the port of postgresql-9.5 on CentOS 6

1. Modify the /var/lib/pgsql/9.5/data/postgresql.conf.

  Uncomment and change listen_addresses = 'localhost' to listen_addresses = '*'.
  Uncomment and change port=5432 to post=<your port>

2. Create or modify the /etc/sysconfig/pgsql/postgresql-9.5 file.

Add the following to the file.
  PGPORT=<your port>

export PGPORT

3. Start the database server.

su - postgres

/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data -l logfile start

4. Launch the psql prompt

psql -p <your port>

---------------------------------------------------------------------------------------------------

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The system used here is PostgreSQL 9.5 on CentOS 6.6.

As the postgres user, you type psql and it gives you such an error.

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

First, check if your database server is running with the following commands

  ps -ef | grep postgres

Which would return something like the following if the server is running.

root 6616 4849 0 14:13 pts/2 00:00:00 su - postgres
postgres 6617 6616 0 14:13 pts/2 00:00:00 -bash
postgres 7219 1 0 15:42 pts/2 00:00:00 /usr/pgsql-9.5/bin/postgres
postgres 7220 7219 0 15:42 ? 00:00:00 postgres: logger process
postgres 7222 7219 0 15:42 ? 00:00:00 postgres: checkpointer process
postgres 7223 7219 0 15:42 ? 00:00:00 postgres: writer process
postgres 7224 7219 0 15:42 ? 00:00:00 postgres: wal writer process
postgres 7225 7219 0 15:42 ? 00:00:00 postgres: autovacuum launcher process
postgres 7226 7219 0 15:42 ? 00:00:00 postgres: stats collector process
postgres 7239 6617 0 15:44 pts/2 00:00:00 ps -ef
postgres 7240 6617 0 15:44 pts/2 00:00:00 grep postgres

If it is not running, use the command below to start it.

  /usr/pgsql-9.5/bin/pg_ctl start

If you are using a different port other than the default 5432, use this command to start psql.

psql -p <port>

---------------------------------------------------------------------------------------------------