Postgresql: Difference between revisions
Jump to navigation
Jump to search
Line 6: | Line 6: | ||
=List databases= | =List databases= | ||
1 | ==Alternative 1== | ||
SELECT datname FROM pg_database; | SELECT datname FROM pg_database; | ||
2 | ==Alternative 2== | ||
\l | \l | ||
=select database= | =select database= | ||
\c testdb; | \c testdb; |
Revision as of 11:48, 2 April 2020
psql
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
\l
select database
\c testdb;
show tables
\dt
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;
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))"