Install postgresql 9.1 on Ubuntu 12.04


Another install guide - maybe not super friendly, but it worked.


1) Install

$ sudo apt-get update
$ sudo apt-get install postgresql-9.1

Verify it works
$ sudo invoke-rc.d postgresql status

2) Set password

Now you need to set a password for the postgres database role:
sudo -u postgres psql postgres
\password postgres

3) Install the contrib (contains ssl, text stuff and required stuff used by pgadmin)
Read more?: http://packages.debian.org/wheezy/postgresql-contrib-9.1

$ sudo apt-get install postgresql postgresql-contrib
$ sudo -u postgres psql

postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION


Verify that it was installed:
select * from pg_extension;


3) Optionally restart database server?

sudo /etc/init.d/postgresql restart

4) Create databases

Now create a database
postgres=# CREATE DATABASE mytestdb;
postgres=# \c mytestdb

mytestdb=# CREATE TABLE monkeytest (abe int);

Display the tables
mytestdb=# \d

Insert a row
mytestdb=# INSERT INTO monkeytest (abe) VALUES (77);

Show the rows:
mytestdb=# SELECT * FROM monkeytest;

For help use \h


The passwords would be from a local users (ident authentication mode), but we need to change this, as it's not practical.
$ sudo vim /etc/postgresql/9.1/main/pg_hba.conf

CHANGE THIS:
local   all             all                                     peer
INTO THIS:
local   all             all                                     md5

TO GET STARTED QUICKLY JUST CHANGE TO (only development). It will match all ip's on the host

host   all             all              0.0.0.0/0                md5


In production it could be:
host all all 10.10.29.0/24 trust


=== OPTIONALLY START ===

You also need to set the IP it will listen on like:
sudo vim /etc/postgresql/9.1/main/postgresql.conf 
listen_addresses = '*'

 Or for production you would use something like:
listen_addresses='202.54.1.2 202.54.1.3'

(Optionally) Read more about authentication: http://www.postgresql.org/docs/9.1/static/auth-methods.html
=== OPTIONALLY END ===

sudo /etc/init.d/postgresql restart

5) Create user and test permissions

For using this database, create a new user:
$ sudo -u postgres createuser -P alison
(Optional: To drop again later:)
$ sudo -u postgres dropuser alison

$ sudo -u postgres psql
\c mytestdb
GRANT ALL ON monkeytest TO alison;

 psql -U alison -d mytestdb
mytestdb=> SELECT * FROM monkeytest;

6) Connect to your database

Real port is 5432, but in this case I've set up a port forward (from 7732 -> 5432) to a local virtual machine using VirtualBox
psql -h localhost -p 7732 postgres
another example
psql -h localhost -p 7732 -U alison -d mytestdb

7) Backup that worked

pg_dump -Upostgres --format=c --file=testdb_backup.sqlc testdb
pg_restore -Upostgres --verbose --format=c testdb_backup.sqlc


=======

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

Comments

Post a Comment

Popular posts from this blog

Switching from Docker Desktop to Rancher Desktop on WSL2: Solving the Missing docker-credential-desktop.exe Error

Switching from Docker Desktop to Rancher Desktop on WSL2: Solving the Missing docker-credential-desktop.exe Error I’ve been happily running Kamal inside WSL2 on my home Windows desktop for a while. My original setup included an older Docker Desktop installation. Recently, I wanted to replicate this environment on a different machine, but I discovered that Docker Desktop had changed their licensing terms. Technically, I’m still covered by the new licensing terms, but I decided to try Rancher Desktop instead. Installing Rancher Desktop on WSL2 Installation was straightforward. I installed Rancher Desktop, configured it to integrate with WSL2, and disabled Kubernetes (since I don’t currently need it). My plan was to continue using the Docker CLI inside WSL2 alongside Rancher Desktop for container management. The Authentication Error However, when I was in WSL and tried to authenticate (for example, with Docker Hub via docker login), I ran into this error: ``` failed to store...
Read more

Running LXD/LXC on WSL2 with Ubuntu 20.04

Download Ubuntu 20.04 from the Microsoft Store sudo apt-get update sudo apt-get upgrade cd ~ git clone https://github.com/DamionGans/ubuntu-wsl2-systemd-script.git cd ubuntu-wsl2-systemd-script/ bash ubuntu-wsl2-systemd-script.sh sudo apt-get install snap (exit the shell and reopen it) Source: https://github.com/damionGans/ubuntu-wsl2-systemd-script To the IPv6 question I answer "none". $ sudo lxd init 2020/08/01 15:55:13 usbid: failed to load: open /usr/share/misc/usb.ids: no such file or directory Would you like to use LXD clustering? (yes/no) [default=no]: Do you want to configure a new storage pool? (yes/no) [default=yes]: Name of the new storage pool [default=default]: Name of the storage backend to use (ceph, btrfs, dir, lvm) [default=btrfs]: Create a new BTRFS pool? (yes/no) [default=yes]: Would you like to use an existing block device? (yes/no) [default=no]: Size in GB of the new loop device (1GB minimum) [default=50GB]: Would you like to connect to a MAAS server? (...
Read more

Proxmox Remote administration via VNC

Image
A few times I've needed to communicate via VNC, for instance when I was changing network configuration and couldn't use SSH or when I tried out Windows. Communicating with VNC to Proxmox wasn't so easy for me as it seemed in the documentation. Basically I never really got the " /etc/inetd.conf " thing to work, but this made it work from my local machine. This blog post is an alternative to the openbsd-inetd solution In the following: 2.2.2.2 is the IP of my Proxmox host 100 is the VM-ID you picked when you created the virtual machine ======================================================================== ssh -f -L 25901:127.0.0.1:5910 root@ 2.2.2.2 'nc -l -p 5910 -c "qm vncproxy 100 "'; "/Applications/TigerVNC Viewer 1.2.0.app/Contents/MacOS/TigerVNC Viewer" localhost:25901 ======================================================================== Please note that I only got TigerVNC to work, since all the other VNCs ha...
Read more