Postgresql

Restore dump

  psql -U postgres -d bpc -f bpc-2012-11-14.sql 

http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/

Change postgres user password

http://scratching.psybermonkey.net/2009/06/postgresql-how-to-reset-user-name.html

  sudo vim /etc/postgresql/9.1/main/pg_hba.conf 

Make auth method trust.

  sudo /etc/init.d/postgresql restart

  sudo psql -U postgres
postgres=# ALTER USER postgres with password 'new_password';

Replace auth method back.

Option to run commands with TCP method

http://stackoverflow.com/questions/2942485/psql-fatal-ident-authentication-failed-for-user-postgres

-h

$ psql -U postgres -h localhost

Change password for postgres user

$ sudo -u postgres psql
\password
Enter password: ...
...

Configuring to use password not PEER auth

https://help.ubuntu.com/10.04/serverguide/postgresql.html

After configuring the password, edit the file /etc/postgresql/8.4/main/pg_hba.conf to use MD5 authentication with the postgres user:

  local   all         postgres                          md5

sudo /etc/init.d/postgresql-8.4 restart

Drop (delete) user

dropuser -U postgres username

Allow a user create database

http://stackoverflow.com/questions/8988495/postgres-user-create-database

ALTER USER your_username CREATEDB;

Change postgres user password

http://archives.postgresql.org/pgsql-novice/2007-02/msg00062.php

Assuming you still have TRUST authentication setup on the local server, open a psql session as the postgres user (you won't be asked for the password under TRUST authentication) to the database and execute the command:

sudo -s
sudo -u postgres psql 
ALTER USER Postgres WITH PASSWORD '<newpassword>';

Quit pqsl

http://my.safaribooksonline.com/book/databases/sql/0321334175/postgresql/ch01lev2sec25

Type \q and press Enter.

Tutorial

Manuals: http://www.postgresql.org/docs/manuals/

Dropping a role

http://articles.slicehost.com/2009/5/7/postgresql-creating-and-deleting-roles

What if we want to drop (delete, remove) a role? Easy:

#DROP ROLE demorole1;

http://www.postgresql.org/docs/9.1/static/database-roles.html

For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:

createuser name
dropuser name
sudo -u postgres createuser -P django

Create database

To create the first database, which we will call "mydb", simply type:

sudo -u postgres createdb mydb

Drop a database

DROP DATABASE [ IF EXISTS ] name

Ubuntu

https://help.ubuntu.com/community/PostgreSQL

sudo apt-get install postgresql

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

sudo -u postgres psql postgres

Set a password for the "postgres" database role using the command:

\password postgres
Last edited by Artem Dudarev, 2013-10-13 02:01:37. Edit