...
During
...
the
...
time
...
of
...
this
...
article,
...
the
...
...
...
...
...
...
were
...
pretty
...
light
...
and
...
from
...
what
...
I
...
can
...
see
...
not
...
quite
...
complete.
...
These
...
instructions
...
are
...
currently
...
written
...
for
...
PostgreSQL
...
8.x.
...
Installing
...
PostgreSQL
Ensure UTF-8
...
Encoding
...
is
...
Set
...
Make
...
sure
...
that
...
your
...
operating
...
system
...
has
...
the
...
right
...
encoding
...
set
...
as
...
explained
...
in
...
the
...
"Select
...
Locale"
...
part
...
of
...
my
...
...
...
...
...
article.
...
In
...
the
...
case
...
of
...
my
...
Ubuntu
...
server
...
it
...
was
...
installed
...
with
...
the
...
minimal
...
configuration
...
and
...
was
...
missing
...
the
...
utf-8
...
locale.
...
Install
The Ubuntu install is simply,
| Code Block |
|---|
sudo apt-get install postgresql # installs postgresql{code}
|
Similar
...
to
...
how
...
Apache
...
is
...
installed,
...
Ubuntu
...
has
...
it's
...
own
...
standard.
...
Here
...
are
...
the
...
keynotes.
...
You
...
will
...
now
...
have
...
a
...
postgresql
...
user
...
on
...
your
...
system.
...
This
...
account
...
is
...
reserved
...
for
...
running
...
Postgresql
...
in
...
a
...
secure
...
manner.
...
This
...
account
...
neither
...
has
...
a
...
password
...
or
...
a
...
home
...
directory.
...
I
...
will
...
show
...
you
...
how
...
to
...
use
...
it
...
as
...
we
...
go
...
forward.
...
The
...
postgresql
...
service
...
is
...
also
...
added
...
to
...
the
...
system
...
to
...
start
...
automatically.
...
Finally
...
here
...
are
...
some
...
directories
...
to
...
be
...
aware
...
of,
...
- /var/lib/postgresql/8.4/main
...
- -
...
- location
...
- of
...
- the
...
- main
...
- database
...
- that
...
- can
...
- only
...
- be
...
- viewed
...
- and
...
- modified
...
- by
...
- the
...
- user
...
- postgresql
...
- .
...
- /etc/postgresql/8.4/main
...
- -
...
- location
...
- of
...
- all
...
- configuration
...
- files.
...
- /var/log/postgresql/
...
- -
...
- location
...
- where
...
- Ubuntu
...
- keeps
...
- all
...
- the
...
- logs
...
- and
...
- automatically
...
- is
...
- set
...
- to
...
- archive
...
- and
...
- roll
...
- over.
...
That's
...
all
...
there
...
is
...
for
...
the
...
installation.
...
Ubuntu
...
apt-get
...
makes
...
it
...
easy
...
and
...
does
...
all
...
the
...
hard
...
work
...
for
...
you.
...
Further Details
You might also be interested to know that Ubuntu's
...
apt-get
...
installer
...
does
...
a
...
number
...
of
...
other
...
things
...
you
...
would
...
have
...
had
...
to
...
manually
...
configure
...
if
...
you
...
installed
...
manually,
...
- Initialize
...
- a
...
- database
...
- storage
...
- area
...
- on
...
- disk
...
- called
...
- the
...
- database
...
- cluster.
...
- ...
...
| Warning |
|---|
In order to complete this section I need to figure out how to do a configured portable setup of PostgreSQL. {warning} h2. Initial Setup h3. Change the Default *postgres* Database User Account If you want to manage postgresql outside of the command line, you must change the default password in postgresql. The first step, is to step into the postgresql shell launching the program psql as the user postgres. {code:lang=bash} |
Initial Setup
Change the Default postgres Database User Account
If you want to manage postgresql outside of the command line, you must change the default password in postgresql.
The first step, is to step into the postgresql shell launching the program psql as the user postgres.
| Code Block | ||
|---|---|---|
| ||
sudo -u postgres psql postgres # -u postgres makes the psql run as the postgres user{code}
|
Here
...
is
...
what
...
it
...
looks
...
like
...
once
...
you
...
are
...
in,
...
| Code Block | ||
|---|---|---|
| ||
psql (8.4.1)
Type "help" for help.
postgres=#{code}
|
All
...
commands
...
are
...
prefixed
...
by
...
the
...
the
...
\.
...
So
...
to
...
change
...
the
...
postgres
...
account
...
password,
...
| Code Block | ||
|---|---|---|
| ||
postgres=# password postgres{code}
|
Note
...
that
...
this
...
changes
...
the
...
account
...
inside
...
of
...
postgres,
...
not
...
the
...
operating
...
system
...
account
...
postgres.
...
Finally
...
exit
...
the
...
postgres
...
shell,
...
| Code Block | ||
|---|---|---|
| ||
postgres=# quit{code}
|
With
...
this
...
change
...
you
...
can
...
now
...
use
...
a
...
client
...
software
...
such
...
as
...
pgAdmin
...
III.
...
Note
...
that
...
be
...
by
...
default,
...
postgres
...
is
...
very
...
secure.
...
As
...
such
...
with
...
this
...
change
...
you
...
will
...
be
...
able
...
to
...
use
...
pgAdmin
...
III
...
in
...
only
...
the
...
following
...
2
...
scenarios,
...
- Using
...
- the
...
- pgAdmin
...
- III
...
- installed
...
- on
...
- the
...
- same
...
- system
...
- as
...
- Postgresql.
...
- From
...
- another
...
- machine
...
- using
...
- an
...
- SSH
...
- Tunnel.
...
Read
...
further
...
for
...
more
...
details
...
on
...
connecting.
...
Connecting
...
to
...
the
...
Database
GUI Client
Besides the command line interface, I like to be able to manage the database using a remote GUI client.
pgAdmin 3 from www.pgadmin.org
...
...
...
is
...
one
...
of
...
the
...
most
...
popular
...
GUI
...
clients.
...
It
...
is
...
cross
...
platform
...
so
...
I
...
happen
...
to
...
run
...
it
...
on
...
my
...
Windows
...
desktop.
...
| Tip |
|---|
Note: for the nit picky users like me, in the Windows environment I personally hate installers, so I install this in a test machine, copy out the directory and then just use on my other systems without having to install and have strange things in my Windows registry. {tip} |
From
...
my
...
Windows
...
desktop
...
I
...
SSH
...
into
...
the
...
Ubuntu
...
server
...
and
...
tunnel
...
port
...
of
...
5432
...
to
...
connect.
...
See,
...
...
...
...
...
...
...
for
...
more
...
details.
...
I
...
find
...
using
...
SSH
...
to
...
be
...
the
...
most
...
secure
...
method.
...
In
...
some
...
cases
...
you
...
may
...
need
...
to
...
...
...
...
.
Server Instrumentation Warning
Note, the very first time I connected with pgAdmin 3 I got a warning on Server instrumentation. I am currently trying to understand if these messages should be addressed or ignored. My gut is saying that I do not need this in a stripped down system. It's most likely nice to have tools for DB admins to get more data.
Here is the warning,
| Warning |
|---|
Server instrumentation The server lacks instrumentation functions. pgAdmin III uses some support functions that are not available by default in all PostgreSQL versions. These enable some tasks that make life easier when dealing with log files and configuration files. When compiling from source, the necessary files can be found in the xtra subdirectory of the pgAdmin source tree. For PostgreSQL 8.0, copy the admin directory under the postgresql contrib source directory, make and make install from there. For PostgreSQL 8.1, use the admin81 directory for that. PostgreSQL 8.2 and above include the instrumentation functions in the "adminpack" contrib module. After the module is installed, you need to create the instrumentation functions in your maintenance database using the admin.sql script (admin81.sql for PostgreSQL 8.1) which are usually located in the pgsql share directory (e.g. /usr/local/pgsql/share)
server instrumentation is not supported for PostgreSQL 7.3 and 7.4. {warning} h2. Backup of a Database {warning}This section needs to be written and tested.{warning} [ |
Backup of a Database
| Warning |
|---|
This section needs to be written and tested. |
http://www.postgresql.org/docs/8.1/static/backup.html
...
-
...
not
...
too
...
clear,
...
for
...
example
...
how
...
do
...
I
...
specify
...
a
...
user?
...
...
-
...
looks
...
promising,
...
syntax
...
looks
...
very
...
different
...
from
...
official
...
manual
...
though.
...
Also,
...
there
...
is
...
a
...
backup
...
interface
...
via
...
the
...
gui...
...
figure
...
out
...
how
...
to
...
get
...
the
...
command
...
line
...
equivalent.
...
It
...
worked
...
well
...
enough
...
for
...
my
...
52MB
...
database.
...
Just
...
took
...
a
...
while
...
as
...
it
...
went
...
over
...
the
...
network
...
(gui
...
ran
...
from
...
a
...
client
...
machine).