My personal notes on all things technical and factual....

Clayton's Tech Bits

Search this site:

Custom Search

Categories:

/ (224)
  Admin/ (86)
    Apache/ (7)
      HTTPS-SSL/ (4)
    Cherokee/ (1)
    LAN/ (4)
    LVM/ (3)
    Monitoring/ (2)
      munin/ (2)
    OpenVPN/ (1)
    SSH-Proxy/ (3)
    SSH-SSL/ (6)
    backups/ (16)
      SpiderOak/ (1)
      backuppc/ (5)
      dirvish/ (1)
      misc/ (6)
      rdiff-backup/ (1)
      rsync/ (1)
      unison/ (1)
    commandLine/ (11)
    crontab/ (1)
    databases/ (8)
      MSSQL/ (2)
      MySQL/ (5)
      PostgreSQL/ (1)
    dynamicDNS/ (2)
    email/ (9)
      Dovecot/ (1)
      deliverability/ (1)
      misc/ (1)
      postfix/ (6)
    iptables/ (2)
    virtualization/ (8)
      VMware/ (1)
      virtualBox/ (7)
  Coding/ (11)
    bash/ (1)
    gdb/ (1)
    git/ (2)
    php/ (4)
    python/ (3)
      Django/ (1)
  Education/ (1)
  Hosting/ (23)
    Amazon/ (14)
      EBS/ (3)
      EC2/ (11)
    Godaddy/ (2)
    NearlyFreeSpeech/ (3)
    Rackspace/ (1)
    vpslink/ (3)
  Linux/ (20)
    Awesome/ (3)
    CPUfreq/ (1)
    Chinese/ (1)
    Debian/ (5)
      WPA/ (1)
    audio/ (1)
    encryption/ (2)
    fonts/ (1)
    misc/ (4)
    router-bridge/ (2)
  SW/ (39)
    browser/ (2)
      Chrome/ (1)
      Firefox/ (1)
    business/ (25)
      Drupal/ (8)
      KnowledgeTree/ (6)
      Redmine/ (2)
      SugarCRM/ (6)
      WebERP/ (2)
      eGroupware/ (1)
    email/ (1)
    fileSharing/ (1)
      mldonkey/ (1)
    graphics/ (2)
    research/ (2)
    website/ (6)
      blog/ (6)
        blosxom/ (3)
        rss2email/ (1)
        webgen/ (1)
  Security/ (12)
    IMchat/ (1)
    circumvention/ (2)
    e-mail/ (4)
    greatFirewall/ (1)
    hacking/ (1)
    password/ (1)
    privacy/ (1)
    skype/ (1)
  Services/ (1)
    fileSharing/ (1)
  TechWriting/ (1)
  xHW/ (13)
    Lenovo/ (1)
    Motorola_A1200/ (2)
    Thinkpad_600e/ (1)
    Thinkpad_a21m/ (3)
    Thinkpad_i1300/ (1)
    Thinkpad_x24/ (1)
    USB_audio/ (1)
    scanner/ (1)
    wirelessCards/ (2)
  xLife/ (17)
    China/ (9)
      Beijing/ (5)
        OpenSource/ (3)
    Expatriation/ (1)
    Vietnam/ (7)

Thu, 07 Apr 2011

/Admin/databases/PostgreSQL: PostgreSQL Cheat Sheet

/etc/postgresql/9.0/main/pg_hba.conf specifies the users that can access PostgreSQL. In Debian, the postgres user is the default. Therefore, to create a database[2]:

sudo su
su postgres
createdb dbname

Create a user and grant privileges on the new database:

su postgres
createuser uname
psql dbname
GRANT ALL PRIVILEGES ON DATABASE dbname TO uname;

To create a database and user with SQL:

su postgres
psql
CREATE DATABASE dbname;
CREATE USER uname WITH PASSWORD 'pswd';
GRANT ALL PRIVILEGES ON DATABASE dbname TO uname;

To dump and then restore a database[1]:

pg_dump dbname > outfile
psql dbname < infile

Simple SQL:

psql
\c dbname # connect to a database (similar to "use dbname;" in MySQL)
\dt # list all tables in database
\d tname # show table structure / columns of table tname
SELECT * FROM tname; # list contents of table tname
SELECT * FROM tname WHERE colname='string'; # select on column contents exactly
SELECT * FROM tname WHERE colname ~ 'string*'; # select using regular expression
UPDATE tname SET colname='newstring' WHERE colname='oldstring';

[1] http://www.postgresql.org/docs/9.0/interactive/backup-dump.html
[2] http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html

posted at: 07:18 | path: /Admin/databases/PostgreSQL | permanent link to this entry

Home

Contact

Resumé / C.V.

Links

Categories:

Archives:

Subscribe XML RSS Feed

Thu, 07 Apr 2011