Skip to content

Instantly share code, notes, and snippets.

@mainul35

mainul35/Postgres important commands.md

Last active September 12, 2020 06:54
Show Gist options
  • Save mainul35/38cf2c67189070c3be2d78cc75c95c94 to your computer and use it in GitHub Desktop.
Save mainul35/38cf2c67189070c3be2d78cc75c95c94 to your computer and use it in GitHub Desktop.
Download ZIP
Postgresql important commands for PSQL DB management and queries
Raw
Postgres important commands.md

Login to PostgreSQL from terminal with Postgres DB user

Note that, in following lines, you have first entered into postgres user't terminal (Of your OS) root Then by using psql command, you have entered into the postgres DB user's shell

[mainul35@localhost ~]$ sudo su postgres
bash-5.0$ psql
could not change directory to "/home/mainul35": Permission denied
psql (11.7)
Type "help" for help.

postgres=#

Exit from Postgres DB user shell and Postgres system user

postgres=# \q
bash-5.0$ exit;
exit
[mainul35@localhost ~]$

Create Database

postgres=# CREATE DATABASE chat_app;

Show Databases

postgres=# \l
                                        List of databases
         Name          |  Owner   | Encoding |   Collate   |    Ctype    |   Acc
ess privileges   
-----------------------+----------+----------+-------------+-------------+------
-----------------
 chat_app              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 demonstrating_oauth   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mainul35              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 qa_board              | mainul35 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 student_course_module | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/po
stgres          +
                       |          |          |             |             | postg
res=CTc/postgres
 template1             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/po
stgres          +
                       |          |          |             |             | postg
res=CTc/postgres
 victoryapp            | mainul35 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(9 rows)

postgres=#

Connect to some Database

postgres=# \c chat_app
You are now connected to database "chat_app" as user "postgres".
chat_app=#

Show schemas

chat_app=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

chat_app=#

Create Schema and change owner

chat_app=# create schema chat_app;
CREATE SCHEMA
chat_app-# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 chat_app | postgres
 public   | postgres
(2 rows)

chat_app=# ALTER SCHEMA chat_app OWNER TO mainul35;
ALTER SCHEMA
chat_app=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 chat_app | mainul35
 public   | postgres
(2 rows)

Create Table under chat_app schema and show them

chat_app=# \dt chat_app.
                List of relations
  Schema  |        Name        | Type  |  Owner   
----------+--------------------+-------+----------
 chat_app | ewsd_tbl_authority | table | postgres
 chat_app | user_auth_details  | table | postgres
(2 rows)

chat_app=# create table chat_app.test(id integer);
CREATE TABLE
chat_app=# \dt chat_app.
                List of relations
  Schema  |        Name        | Type  |  Owner   
----------+--------------------+-------+----------
 chat_app | ewsd_tbl_authority | table | postgres
 chat_app | test               | table | postgres
 chat_app | user_auth_details  | table | postgres
(3 rows)

chat_app=#

Drop Table

chat_app=# drop table chat_app.test;
DROP TABLE
chat_app=# \dt chat_app.
                List of relations
  Schema  |        Name        | Type  |  Owner   
----------+--------------------+-------+----------
 chat_app | ewsd_tbl_authority | table | postgres
 chat_app | user_auth_details  | table | postgres
(2 rows)

chat_app=#

Troubleshooting:

FATAL: Ident authentication failed for user - Unable to connect to PostgreSQL

We often get this error when we try to connect our postgresql server from some 3rd party client or software we develop that is going to use PostgreSQL as database.

Symptom

2014-08-29 21:10:09,102 ERROR [localhost-startStop-1]  c.a.j.s.w.c.JohnsonContextLoaderListener The Spring WebApplicationContext could not be started
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'crowdUserDao' defined in class path resource [crowd-spi-context.xml]: Unsatisfied dependency expressed through constructor argument with index 0 of type [org.springframework.transaction.PlatformTransactionManager]: : Error creating bean with name 'pluginConfiguration' defined in class path resource [bitbucket-context.xml]: Cannot resolve reference to bean 'pluginPersistentStateStore' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'pluginPersistentStateStore' defined in class path resource [bitbucket-context.xml]: Cannot create inner bean 'com.atlassian.bitbucket.internal.plugin.DefaultPluginPersistentStateStore#154a49f' of type [com.atlassian.bitbucket.internal.plugin.DefaultPluginPersistentStateStore] while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.atlassian.bitbucket.internal.plugin.DefaultPluginPersistentStateStore#154a49f' defined in class path resource [bitbucket-context.xml]: Cannot resolve reference to bean 'pluginStateDao' while setting constructor argument; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'pluginStateDao' defined in URL [jar:file:/opt/atlassian/bitbucket-3.0.4/atlassian-bitbucket/WEB-INF/lib/bitbucket-dao-impl-3.0.4.jar!/com.atlassian.bitbucket/internal/plugin/HibernatePluginStateDao.class]: Unsatisfied dependency expressed through constructor argument with index 0 of type [org.hibernate.SessionFactory]: : Error creating bean with name 'databaseSupplier': Invocation of init method failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:postgresql://localhost:5432/bitbucket, username = atlas. Terminating connection pool. Original Exception: ------
org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "atlas"
    at org.postgresql.Driver$ConnectThread.getResult(Driver.java:348)
    at org.postgresql.Driver.connect(Driver.java:271)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:187)
    at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:254)
    at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:305)
    at com.jolbox.bonecp.BoneCPDataSource.maybeInit(BoneCPDataSource.java:150)
    at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:112)
    at com.atlassian.bitbucket.internal.hibernate.DelegatingSwappableDataSource.getConnection(DelegatingSwappableDataSource.java:48)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier.databaseFor(DefaultDatabaseSupplier.java:180)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier.detailsFor(DefaultDatabaseSupplier.java:195)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier.access$500(DefaultDatabaseSupplier.java:53)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier$1.create(DefaultDatabaseSupplier.java:83)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier$1.create(DefaultDatabaseSupplier.java:79)
    at com.atlassian.util.concurrent.ResettableLazyReference$InternalReference.create(ResettableLazyReference.java:140)
    at com.atlassian.util.concurrent.LazyReference$Sync.run(LazyReference.java:321)
    at com.atlassian.util.concurrent.LazyReference.getInterruptibly(LazyReference.java:143)
    at com.atlassian.util.concurrent.LazyReference.get(LazyReference.java:112)
    at com.atlassian.util.concurrent.ResettableLazyReference.get(ResettableLazyReference.java:87)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier.get(DefaultDatabaseSupplier.java:93)
    at com.atlassian.bitbucket.internal.db.DefaultDatabaseSupplier.validate(DefaultDatabaseSupplier.java:153)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:349)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:300)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:133)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:407)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1545)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:294)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:294)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:960)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:858)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:811)
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:739)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:185)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1114)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:108)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:646)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:140)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1114)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveInnerBean(BeanDefinitionValueResolver.java:276)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:122)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:646)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:140)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1114)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:328)
    at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:108)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveConstructorArguments(ConstructorResolver.java:632)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:140)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1114)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getSingletonFactoryBeanForTypeCheck(AbstractAutowireCapableBeanFactory.java:845)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:776)
    at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:543)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:387)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:362)
    at org.springframework.beans.factory.BeanFactoryUtils.beanNamesForTypeIncludingAncestors(BeanFactoryUtils.java:187)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1002)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:960)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:858)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:811)
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:739)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:185)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1114)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1017)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:504)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:703)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:760)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:403)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
    at com.atlassian.johnson.spring.web.context.JohnsonContextLoaderListener.initWebApplicationContext(JohnsonContextLoaderListener.java:111)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:106)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4971)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5467)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
------
; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'databaseSupplier': Invocation of init method failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:postgresql://localhost:5432/bitbucket, username = atlas. Terminating connection pool. Original Exception: ------
org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "atlas"
    at org.postgresql.Driver$ConnectThread.getResult(Driver.java:348)
    at org.postgresql.Driver.connect(Driver.java:271)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:187)
    at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:254)
    at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:305)
    at com.jolbox.bonecp.BoneCPDataSource.maybeInit(BoneCPDataSource.java:150)
    at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:112)
    at com.atlassian.bitbucket.internal.hibernate.DelegatingSwappableDataSource.getConnection(DelegatingSwappableDataSource.java:48)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Da...

Cause

The JDBC client login uses 'Ident-based authentication' method instead of 'password authentication' mode.

Resolution

By default PostgreSQL uses IDENT-based authentication and this will never allow you to login via -U and -W options. Allow username and password based authentication from your application by appling 'trust' as the authentication method for the database user. You can do this by modifying the pg_hba.conf file.

You can identify the location of the pg_hba.conf file by running the following command in psql command line, you'll need to be logged in as a superuser in the database:

postgres=# show hba_file ;
 hba_file
--------------------------------------
 /etc/postgresql/9.3/main/pg_hba.conf
(1 row)

This file controls:

  • Which hosts are allowed to connect
  • How clients are authenticated
  • Which PostgreSQL user names they can use
  • Which databases they can access

For example:

local	all	all	trust
host	all	127.0.0.1/32	trust

Don't forget to restart PostgreSQL after saving your changes to the file.

$ service postgresql restart
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment