PostgreSQL Commands

connect into postgres(default) database:

psql –U postgres

connect other db server in command prompt:

set PGPASSWORD=XXX
psql -h hostname -p 5432 -d databaseName -U userName -W

https://stackoverflow.com/questions/32824388/postgresql-remotely-connecting-to-postgres-instance-using-psql-command

show installed locale:

SHOW LC_COLLATE

making backup file:

pg_dump -U postgres SID/database/server's name> dbexportname.pgsql

backup by tar file(better for binary data):

pg_dump -U [yourUserName] -h [yourHostName] -F t -f xxx.tar [yourDataBaseName]

restore by tar file:

pg_restore -U [yourUserName] -h [yourHostName] -d [yourDatabaseName] -F t xxx.tar

restore from dump file:

psql -U accountName databaseName < backupName.pgsql

list all database:

\l

check databasename:

\db+

show main data directory:

show data_directory;

check password lifetime:

https://www.modb.pro/db/103980#51_pg_userpg_authidpg_rolespg_auth_members_305

\d pg_user

check the user’s capacity:

select * from pg_user where usename='yourUserName';

select * from pg_authid;

select * from pg_authid where rolvaliduntil is null;

pset null ‘null’;

select rolename,rolvaliduntil from pg_authid;

check the charactor code:

show server_encoding;

clear screen:

\! cls

use db account postgres to log into the spotfire_server db:

psql -d spotfire_server -U postgres

check postgreSQL version:

psql -V

check settings like:

select setting from pg_settings where name='port';

https://qiita.com/awakia/items/9981f37d5cbcbcd155eb

List all tables in postgresql information_schema:

https://stackoverflow.com/questions/2276644/list-all-tables-in-postgresql-information-schema

select * from pg_tables where schemaname = 'information_schema';

create a database without dbcreate privilege and user:

eg:

createuser spotfire_joindb with password 'spotfire' nocreatedb;
create database spotfire_joindb;
alter database spotfire_joindb owner to spotfire_joindb;

check the os: select version();

calculate row size of postgres table row:

https://stackoverflow.com/questions/34222996/how-to-get-each-row-size-of-a-particular-table-in-postgresql?answertab=active#tab-top

SELECT sum(pg_column_size(t.)) as filesize, count() as filerow FROM tableName as t;

check the column’s data type:

select pg_typeof(colName) from tableName;

show the data table in a certain database:

\dt

delete table:

DROP TABLE test;

delete database:

drop database databaseName;

Change default PostgreSQL passwords

cancel peer authentication:

change the content in /etc/postgresql/13/main/pg_hba.conf as below:

# "local" is for Unix domain socket connections only
local   all             all                                     md5

set listen inbound port:

change the content in /etc/postgresql/13/main/postgresql.conf:

listen_addresses = '*'               # what IP address(es) to listen on;

set inbound IP address:

change the content in /etc/postgresql/13/main/pg_hba.conf:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24            md5

PostgreSQL data file folder: show data_directory;