Project

General

Profile

Feature #676

Common PostgreSQL Commands on FreeBSD

Added by Daniel Curtis almost 5 years ago. Updated over 4 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Database Server
Target version:
Start date:
10/18/2015
Due date:
% Done:

100%

Estimated time:
0.50 h
Spent time:

Description

  • 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

History

#1 Updated by Daniel Curtis almost 5 years ago

  • Status changed from New to In Progress
  • Start date changed from 10/17/2015 to 10/18/2015
  • % Done changed from 0 to 50

#2 Updated by Daniel Curtis almost 5 years ago

  • Description updated (diff)

#3 Updated by Daniel Curtis almost 5 years ago

  • Description updated (diff)

#4 Updated by Daniel Curtis almost 5 years ago

  • Status changed from In Progress to Resolved
  • % Done changed from 50 to 100

#5 Updated by Daniel Curtis over 4 years ago

  • Description updated (diff)

#6 Updated by Daniel Curtis over 4 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF