Feature #676
Updated by Daniel Curtis over 8 years ago
{{>toc}}
* Login to postgresql locally:
<pre>
su - pgsql
psql -d template1
</pre>
#* Or connect remotely:
<pre>
psql -h pg.example.com -d somedb -U someuser -W
</pre>
---
h2. Database Commands
* Determine system tables:
<pre>
select * from pg_tables where tableowner = 'postgres';
</pre>
* List databases:
<pre>
\l
</pre>
* Describe a table:
<pre>
\d sometable
</pre>
* Quit psql shell:
<pre>
\q
</pre>
* Switch postgres database:
<pre>
\connect someotherdb
</pre>
* Show all tables:
<pre>
\dt
</pre>
* List all Schemas:
<pre>
\dn
</pre>
* Show database version:
<pre>
SELECT version();
</pre>
* Create a database with an owner:
<pre>
CREATE DATABASE somedb OWNER someuser;
</pre>
* Change database owner:
<pre>
alter database somedb owner to someuser;
</pre>
* Copy a database:
<pre>
CREATE DATABASE newdb WITH TEMPLATE somedb;
</pre>
* View database connections:
<pre>
SELECT * FROM pg_stat_activity;
</pre>
* View show data directory:
<pre>
show data_directory;
</pre>
* Show run-time parameters:
<pre>
show all;
select * from pg_settings;
</pre>
---
h2. Backup/Restore Commands
* Backup database into file:
<pre>
pg_dump -W -U someuser -h pg.example.com somedb > somedb_dump.sql
</pre>
* Load data into posgresql:
<pre>
psql -W -U someuser -h pg.example.com -d somedb < somedb_dump.sql
</pre>
#* Restore postgresql dump file:
<pre>
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
</pre>
---
h2. User Commands
* List all users:
<pre>
\du
</pre>
* Create new user from the postgresql shell:
<pre>
CREATE USER someuser WITH PASSWORD 'SuperSecretPassword';
</pre>
#* Or from the command line
<pre>
sudo -u pgsql createuser someuser -W
</pre>
* Change user password:
<pre>
ALTER USER someuser WITH PASSWORD 'NewSecretPassword';
</pre>
* Grant user createdb privilege:
<pre>
ALTER USER someuser WITH createdb;
</pre>
* Create a superuser user from the postgresql shell:
<pre>
create user somesuperuser with password 'SuperSecretPassword' SUPERUSER
</pre>
#* or with more privileges:
<pre>
create user somesuperuser with password 'SuperSecretPassword' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;
</pre>
#* or from the command line
<pre>
sudo -u pgsql postgres createuser somesuperuser -W -s
</pre>
* Upgrade an existing user to superuser:
<pre>
alter user somesuperuser with superuser;
</pre>
#* or with more privileges
<pre>
alter user somesuperuser with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION
</pre>
* Grant readonly privileges to all tables for someuser
<pre>
grant select on all tables in schema public to someuser;
</pre>
* Grant all privileges on table1, table2, and table3 to myuser
<pre>
grant all privileges on table1, table2, table3 to myuser;
</pre>
---
h2. Resources
* http://jazstudios.blogspot.com/2010/06/postgresql-login-commands.html