install postgres 9.3 on ubuntu 12.04   3 comments


# http://www.gistutor.com/postgresqlpostgis/6-advanced-postgresqlpostgis-tutorials/32-setting-up-pgadmin-server-instrumentation.html
sudo vim /etc/postgresql/9.1/main/postgresql.conf
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’

# https://help.ubuntu.com/community/PostgreSQL
sudo -u postgres psql < /usr/share/postgresql/8.4/contrib/adminpack.sql
$ sudo -u postgres psql < /usr/share/postgresql/9.1/extension/adminpack–1.0.sql
Use "CREATE EXTENSION adminpack" to load this file.

# http://www.gistutor.com/postgresqlpostgis/6-advanced-postgresqlpostgis-tutorials/32-setting-up-pgadmin-server-instrumentation.html
$ echo "CREATE EXTENSION adminpack" | su postgres -c "psql -U postgres postgres";
su: must be run from a terminal

# http://archives.postgresql.org/pgsql-general/2012-03/msg00202.php
$ psql -U postgres postgres
psql: FATAL: Peer authentication failed for user "postgres"
That says you're trying to log in as postgres and the OS reports that you're not postgres.
You either need to su to postgres or change your authentication method.

$ sudo -u postgres psql postgres
postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION
postgres=# psql < /usr/share/postgresql/9.1/extension/adminpack–1.0.sql
postgres-# ;
ERROR: syntax error at or near "psql"
LINE 1: psql < /usr/share/postgresql/9.1/extension/adminpack
^

postgres-# psql < /usr/share/postgresql/9.1/extension/adminpack–1.0.sql;
postgres-#

———————————————————————–
° How to install Postgresql 9.1 on Ubunut 12.04?
sudo apt-get install postgresql-9.1
sudo apt-get install postgresql-contrib-9.1

———————————————————————–
° How to start / stop the server?
$ sudo service postgresql start / stop
$ sudo /etc/init.d/postgresql start / stop

———————————————————————–
° How to create a user ?
sudo -u postgres psql
postgres=# CREATE USER jiradbuser WITH PASSWORD 'jiradbuser';

or, from command line:
max@max-desktop:~$ createuser -S -d -r -P -E jiradbuser

———————————————————————–
° How to change a user password ?
ALTER USER Postgres WITH PASSWORD '';

———————————————————————–
° How to create a db?
postgres=# CREATE DATABASE jiradb WITH ENCODING ‘UNICODE';
postgres=# GRANT ALL PRIVILEGES ON DATABASE jiradb to jiradbuser;

or, from command line:
max@max-desktop:~$ createdb -E UNICODE jiradb
max@max-desktop:~$ createdb –owner jiradbuser –encoding utf8 jiradb

———————————————————————–
° How to connect to a db with a specific user?
# su – mypguser
$ psql mypgdatabase
or, from command line:
psql -d mypgdatabase -U mypguser

———————————————————————–
° How to config tcp connections?
/etc/postgresql/9.1/main/pg_hba.conf

/etc/postgresql/9.1/main/postgresql.conf
———————————————————————–
° How to install extensions?
postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION

———————————————————————–
== My Steps ==
———————————————————————–
sudo -u postgres psql
postgres=# CREATE EXTENSION adminpack;
CREATE EXTENSION
postgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD ‘mypassword';
postgres=# \q

sudo -u postgres psql
postgres=# CREATE USER jiradbuser WITH PASSWORD ‘mypassword';
postgres=# CREATE DATABASE jiradb WITH OWNER jiradbuser ENCODING ‘UNICODE';
postgres=# GRANT ALL PRIVILEGES ON DATABASE jiradb to jiradbuser;

max@max-desktop:~$ psql -U jiradbuser -p 5432 jiradb
psql: FATAL: Peer authentication failed for user “jiradbuser”

max@max-desktop:~$ psql -h localhost -U jiradbuser jiradb (use ipv6)
Password for user jiradbuser:

max@max-desktop:~$ psql -h 127.0.0.1 -U jiradbuser jiradb (use ipv4)
Password for user jiradbuser:

max@max-desktop:~$ psql -U jiradbuser jiradb (unix domain socket)
psql: FATAL: no pg_hba.conf entry for host “[local]”, user “jiradbuser”, database “jiradb”, SSL off
vim /var/log/postgresql/postgresql-9.1-main.log

max@max-desktop:/etc/postgresql/9.1/main$ sudo vim pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres peer
local jiradb jiradbuser md5 #<<– added this line

# TYPE DATABASE USER ADDRESS METHOD

max@max-desktop:~$ psql -U jiradbuser jiradb
Password for user jiradbuser:

About these ads

Posted 26 May 2012 by zxmax in install

Tagged with , , , ,

3 responses to “install postgres 9.3 on ubuntu 12.04

Subscribe to comments with RSS.

  1. Thanks a lot for utilizing time to compose “install postgres 9.

    3 on ubuntu 12.04 Zxmax’s Blog”. Many thanks yet again ,Edgardo

  2. In heading it says postgres 9.3 on ubuntu 12.04, but I can see only postgres 9.1 here.
    How to install postgres 9.3 adminpack?
    CREATE EXTENSION adminpack; gives me error message:
    ERROR: could not open extension control file “/usr/share/postgresql/9.3/extension/adminpack.control”:
    No such file or directory.
    When I try sudo apt-get install postgresql-contrib, Ubuntu 12.04 gives:
    The following extra packages will be installed:
    libossp-uuid16 postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1
    Suggested packages:
    uuid locales-all postgresql-doc-9.1 libdbd-pg-perl
    The following NEW packages will be installed:
    libossp-uuid16 postgresql-9.1 postgresql-client-9.1 postgresql-contrib postgresql-contrib-9.1

    But how to get versions 9.3?
    I installed postgres 9.3 and postgis 2.1 as a part of OpenGeo Suite 4.0.2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: