troywill/arch-linux-postgresql-joy

Basic PostgreSQL configuration and usage documentation with Emacs Org mode

Table of Contents

• 1. Inspect the PostgreSQL environment
• 2. How to create users
  • 2.1. Create a superuser with your login name (app-createuser.html)
  • 2.2. Creating new users with the create user client program
    • 2.2.1. Create the user joe as a superuser, and assign a password immediately
  • 2.3. Creating new users with CREATE USER from PostgreSQL interactive terminal
• 3. How to remove users
  • 3.1. Removing users with SQL from PostgreSQL interactive terminal
• 4. How to update users
• 5. Creating new databases
  • 5.1. Create a new database with createdb
  • 5.2. Create a new database with createdb
• 6. How to restart PostgreSQL
  • 6.1. Ubuntu
• 7. Documentation Links
  • 7.1. PostgreSQL: Documentation www.postgresql.org/docs/
• 8. Heroku PostgreSQL
• 9. Ubuntu Unicode encoding = 'UTF8';

# Inspect the PostgreSQL environment

1. Become the postgres user

   $ sudo su - postgres
   

2. Open the PostgreSQL interactive terminal

   $ psql
   

3. Get help with psql commands

   \?
   

4. List the users

   \dg
   

5. List all the databases

   \l
   

How to create users

Create a superuser with your login name (app-createuser.html)

sudo -u postgres createuser --pwprompt --superuser joe

Creating new users with the create user client program

Create the user joe as a superuser, and assign a password immediately

1. Run the create user program

   createuser --pwprompt --superuser --echo joe
   Enter password for new role: xyzzy
   Enter it again: xyzzy
   CREATE ROLE joe PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
   

Creating new users with CREATE USER from PostgreSQL interactive terminal

1. Become the "postgres" user

   sudo su - postgres
   

2. Open the PostgreSQL interactive terminal

   psql
   

3. Run the CREATE USER SQL command

   CREATE ROLE jonathan LOGIN;
   

How to remove users

Removing users with SQL from PostgreSQL interactive terminal

1. Become the "postgres" user

   $ sudo su - postgres
   

2. Open the PostgreSQL interactive terminal

   $ psql
   

3. DROP USER

   DROP USER IF EXISTS joe
   

How to update users http://www.postgresql.org/docs/current/static/sql-alteruser.html

ALTER USER david WITH PASSWORD 'hu8jmn3';
ALTER USER weight WITH PASSWORD 'weight';

Creating new databases

Create a new database with createdb

1. Run creatdb

   createdb demo --owner railsapp
   

Create a new database with createdb

1. Become the postgres user

   sudo su - postgres
   

2. Run the "CREATE DATABASE" SQL command

   CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
   CREATE DATABASE bootstrap_development OWNER bootstrap;
   CREATE DATABASE bootstrap_test OWNER bootstrap;
   CREATE DATABASE bootstrap_production OWNER bootstrap;
   

How to restart PostgreSQL

Ubuntu

service --status-all
sudo service postgresql-8.4  --full-restart

Documentation Links

PostgreSQL: Documentation www.postgresql.org/docs/

• Current Manual

Heroku PostgreSQL

Ubuntu Unicode encoding = 'UTF8';

• See http://jacobian.org/writing/pg-encoding-ubuntu/

• See http://blog.lnx.cx/tag/locales-fix-slicehost-ubuntu/

In order to connect to template0, we need to change that flag:

template1=# UPDATE pg_database SET datallowconn = TRUE
template1-# WHERE datname = 'template0';
UPDATE 1

Now we can connect, and drop the Template1 database in order to replace it with a copy of Template0.

template0=# UPDATE pg_database SET datistemplate = FALSE
template0-# WHERE datname = 'template1';
UPDATE 1

# Risky!!! Backup First!!!
template0=# drop database template1;
DROP
template0=# create database template1 with template = template0;
create database template1 with template = template0 encoding = 'UTF8';
CREATE