Postgresql: Difference between revisions
Jump to navigation
Jump to search
(→roles) |
|||
Line 125: | Line 125: | ||
Which roles exist. | Which roles exist. | ||
\du | \du | ||
=set new role= | |||
set role super-man; | |||
=current role= | |||
user name of current execution context | |||
SELECT current_user; | |||
=session user= | |||
SELECT session_user; | |||
=display privileges= | =display privileges= | ||
\dp | \dp |
Revision as of 15:52, 5 January 2021
psql
run command from cli
psql -c "SELECT datname FROM pg_database;"
Connect to remote database:
export PGPASSWORD=password && psql --host=10.111.222.1 --port=5492 --username=user --no-password postgres
install psql
apt-get install freetds-bin
List databases
Alternative 1
SELECT datname FROM pg_database;
Alternative 2
gives more information too.
\l Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+-------------+----------+-------------+-------------+----------------------- indata_mdb | uc_etl_prod | UTF8 | sv_SE.UTF-8 | sv_SE.UTF-8 | ...
select database
\c testdb;
show tables
List all tables in all schemas
\dt *.*
list tables in current schema
\dt
Or from command line.
su - postgres -c "psql -c \"\\dt \" database"
schema/search_path
List current schema
SHOW search_path;
Change search path.
SET search_path TO myschema, public;
describe table
\d table;
Change view. One line per attribute
\x on
exit/quit
\q
update
UPDATE users SET admin = 1 WHERE user_id = 14002;
Create database
CREATE DATABASE dbname OWNER username;
Delete database
DROP DATABASE dbname;
delete database from cli
dropdb $dbname
Import a database
psql username -h hostname -d dbname < dump.sql
Create user
CREATE USER username WITH PASSWORD 'MYPASS';
Set super privileges
ALTER USER username WITH SUPERUSER;
List users
\du
Deleting user
DROP USER nomusr
Getting help
\? or \h
size of database
SELECT pg_size_pretty( pg_database_size('database') );
size of table
SELECT pg_size_pretty( pg_total_relation_size('tablename') );
clean archives older than 2 days
su postgres -c "/usr/pgsql-9.5/bin/pg_archivecleanup /var/lib/pgsql/9.5/archivedir/ $(basename $(find /var/lib/pgsql/9.5/archivedir/ -ctime +2 | tail -n 1))"
backup
- Backup database.
DATABASE=very_nice_database ; pg_dump -Fc -Z 2 --file=/tmp/${DATABASE}.$(date '+%Y%m%d').dump ${DATABASE}
restore
Look at permissions on database
psql -c "\l" export DATABASE=very_nice_database
Drop database connectipons.
psql -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${DATABASE}' AND pid <> pg_backend_pid();"
Drop database.
dropdb ${DATABASE}
Create database
createdb -T template0 ${DATABASE}
Restore database
pg_restore -j8 -d ${DATABASE} --no-privileges --no-owner --clean --if-exists --exit-on-error /tmp/${DATABASE}.$(date '+%Y%m%d' --date "-4 days").dump
- Set correct permissions.
psql -c "ALTER DATABASE ${DATABASE} OWNER TO ${OWNER};"
bookmarks
https://wiki.postgresql.org/wiki/Disk_Usage
clear space
\c database vacuum;
if that doesn't work do:
vacuum: freeze; or vacuum full;
autovacuum
Is autovacuum enabled.
SHOW autovacuum;
count table
SELECT COUNT(*) FROM TABLE_NAME;
If you don't need an exact count, the current statistic from the catalog table pg_class might be good enough and is much faster to retrieve for big tables.
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name';
nuber of connections
su - postgres -c "psql -t -c 'SELECT * FROM pg_stat_activity;'" | wc -l
drop connections
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'dbname' AND pid <> pg_backend_pid();
pg_hba.conf
Connections are configured in this file.
list prepared transactions
select * from pg_prepared_xacts where database='cti_batch';
remove prepared connecion
ROLLBACK PREPARED '131077_AAAAAAAAAAAAAP//rB6PFn41NfBevSfGABG7nTE=_AAAAAAAAAAAAAP//rB6PFn41NfBevSfGABG7pAAAAAIAAAAA';
pager off
\pset pager off
pager
export PAGER=less
config file
.my.cnf
.pgpass hostname:port:database:username:password
roles
Which roles exist.
\du
set new role
set role super-man;
current role
user name of current execution context
SELECT current_user;
session user
SELECT session_user;
display privileges
\dp \z
tablespaces
List tablespaces
SELECT spcname FROM pg_tablespace;