PostgreSQL - tlam/Wiki GitHub Wiki

Configuration:

Access psql:
```
 sudo su - postgres
```
1. Alternatively
```
 psql postgres
```

Reset password for psql postgres user:

 sudo su postgres -c psql template1
 postgres=# ALTER USER postgres WITH PASSWORD 'topsecret';

Reset password for unix postgres user and enter the same password as above:
```
 sudo passwd -d postgres
 sudo su postgres -c passwd
```
Start a postgres server:
```
 sudo postgresql start
```
Location of pg_hba.conf on ubuntu
```
  /etc/postgresql/9.2/main/pg_hba.conf
```

Location of pg_hba.conf on the mac

 /Library/PostgreSQL/9.1/data/pg_hba.conf

Using postgres client only
```
 sudo apt-get install libpq-dev
```
On the mac, set the following under .bash_profile:
```
 export PGHOST=localhost
```

For the following error message:

 could not connect to server: Connection refused
 Is the server running on host "192.168.23.129" and accepting
 TCP/IP connections on port 5432?

Edit /etc/postgresql/9.2/main/postgresql.conf and add followed by a restart:

 listen_addresses = '*'

Connecting to SQLAlchemy without providing username or password:
```
 SQLALCHEMY_DATABASE_URI = 'postgresql:///somedatabase'
```
Uninstall and older version on the mac:
```
 open /Library/PostgreSQL/9.2/uninstall-postgresql.app
```
Uninstall psycopg2 and reinstall it after upgrading postgres.

Updating the port number on mac:

 /etc/postgres-reg.ini
 /Library/PostgreSQL/9.4/data/pg_hba.conf
 /Library/PostgreSQL/9.4/data/postgresql.conf

Other actions

Fast restart
```
 pg_ctl restart -m fast
```

Reloading after making a change to pg_hba.conf

 pg_ctl reload -D /Library/PostgreSQL/9.1/data/

 SELECT pg_reload_conf();

FATAL: could not create shared memory segment: Cannot allocate memory on OS X 10.6
```
 sudo sysctl -w kern.sysv.shmall=65536   #  Also add in /etc/sysctl.conf
```

Slow performance on the mac, consider increasing

 kern.sysv.shmmax=1610612736  # max seg size
 kern.sysv.shmall=393216      # max total shared memory

Backup entire database:

 pg_dump name_of_database > name_of_backup_file

Backup the data of a specific table some_table:

 pg_dump -a -t some_table > /tmp/some_table.sql

Backup the schema of a specific table some_table:

 pg_dump --host localhost --port 5432 --username "postgres" --role "postgres" --schema-only --file "some_table.sql" --table "some_table" "the_database_name"

From a psql shell:

List databases:
```
 \l
```
Connect to a database:
```
 \c database_name
```
List tables in a database:
```
 \d
```
Show table columns:
```
 \d table_name
```
Restoring a dump. Create the database as postgres and change the owner to app user:
```
 ALTER DATABASE local_database_dev OWNER TO new_owner;
```
pass the database name and dump filename
```
 psql local_database_dev < dump.sql
```

Check the size of a database

 select pg_size_pretty(pg_database_size('dbname'));

Run SQL script in psql
```
 \i example.sql
```

List all table that starts with a specific prefix

 SELECT tablename FROM pg_tables WHERE tablename LIKE 'product%';

Checks user permission
```
 SELECT * FROM pg_user;
```
Give user CREATEDB permission
```
 ALTER USER tlam CREATEDB;
```

Grant permission, after connecting to a database:

 GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO tlam
 GRANT ALL ON ALL TABLES IN SCHEMA public TO tlam

Display a procedure
```
 \df+ the_procedure
```
Show procedure source code
```
  \ef the_procedure
```
Check current queries
```
 SELECT query from pg_stat_activity;
```
View triggers
```
 SELECT tgname from pg_trigger;
```

Filter on an attribute in a jsonb column called data:

 SELECT *
 FROM table
 WHERE data->>'some_attribute' = 'some value;

Copying data from a table to a csv and back to another table:

 # In staging server
 COPY country TO ~/country.csv WITH (FORMAT csv);

 # Local db
 COPY country FROM ~/country.csv WITH (FORMAT csv);

Schemas

List all schemas
```
 \dn
```
Check the current search path, it might default to public
```
 SHOW search_path;
```
Switch the search_path to a specific schema
```
 SET search_path TO other_schema
```

Adding new user and db setup

Create a new user with CREATEDB permission:

 CREATE USER nba_user WITH PASSWORD 'nba' CREATEDB;

Give peer authentication to nba_user by adding the following line to /etc/postgresql/9.2/main/pg_hba.conf:
```
 # TYPE  DATABASE        USER            ADDRESS                 METHOD        
 local   all             nba_user        trust
```
Reload the database: sudo /etc/init.d/postgresql reload

Mac GUIs

Reference