GNet Solutions » FreeBSD Administration

• Table of contents
• Database Commands
• Backup/Restore Commands
• User Commands
• Resources

• Login to postgresql locally:
  

  su - pgsql
  psql -d template1
  

  • Or connect remotely:
    

    psql -h pg.example.com -d somedb -U someuser -W
    

---

Database Commands¶

• Determine system tables:
  

  select * from pg_tables where tableowner = 'postgres';
  

• List databases:
  

  \l
  

• Describe a table:
  

  \d sometable
  

• Quit psql shell:
  

  \q
  

• Switch postgres database:
  

  \connect someotherdb
  

• Show all tables:
  

  \dt
  

• List all Schemas:
  

  \dn
  

• Show database version:
  

  SELECT version();
  

• Create a database with an owner:
  

  CREATE DATABASE somedb OWNER someuser;
  

• Change database owner:
  

  alter database somedb owner to someuser;
  

• Copy a database:
  

  CREATE DATABASE newdb WITH TEMPLATE somedb;
  

• View database connections:
  

  SELECT * FROM pg_stat_activity;
  

• View show data directory:
  

  show data_directory;
  

• Show run-time parameters:
  

  show all;
  select * from pg_settings;
  

---

Backup/Restore Commands¶

• Backup database into file:
  

  pg_dump -W -U someuser -h pg.example.com somedb > somedb_dump.sql
  

• Load data into posgresql:
  

  psql -W -U someuser -h pg.example.com -d somedb < somedb_dump.sql
  

  • Restore postgresql dump file:
    

    pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
    

• To backup all databases to a file:
  

  pg_dumpall > full-pg.sql
  

  • And restore the dumped file to another postgresql server:
    

    psql -f full-pg.sql
    

---

User Commands¶

• List all users:
  

  \du
  

• Create new user from the postgresql shell:
  

  CREATE USER someuser WITH PASSWORD 'SuperSecretPassword';
  

  • Or from the command line
    

    sudo -u pgsql createuser someuser -W
    

• Change user password:
  

  ALTER USER someuser WITH PASSWORD 'NewSecretPassword';
  

• Grant user createdb privilege:
  

  ALTER USER someuser WITH createdb;
  

• Create a superuser user from the postgresql shell:
  

  create user somesuperuser with password 'SuperSecretPassword' SUPERUSER
  

  • or with more privileges:
    

    create user somesuperuser with password 'SuperSecretPassword' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;
    

  • or from the command line
    

    sudo -u pgsql createuser somesuperuser -W -s
    

• Upgrade an existing user to superuser:
  

  alter user somesuperuser with superuser;
  

  • or with more privileges
    

    alter user somesuperuser with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION
    

• Grant readonly privileges to all tables for someuser
  

  grant select on all tables in schema public to someuser;
  

• Grant all privileges on table1, table2, and table3 to myuser
  

  grant all privileges on table1, table2, table3 to myuser;
  

---

Resources¶

• http://jazstudios.blogspot.com/2010/06/postgresql-login-commands.html