PosgreSQL administration

Here are some posgresql administrative commands,

1, Connecting a database
#psql -h localhost -p 5432 postgres "admin" which connect the user ‘posgres’ with ‘admin’ as password.
2. Create a user from psql console
postgres=# CREATE USER luntbuild;
3. Change the password of the user
postgres=# ALTER USER luntbuild PASSWORD 'luntbuild‘;
4, Create database which owned by a user
postgres=# CREATE DATABASE luntbuild WITH OWNER luntbuild;
5. Importing script to a database
postgres=# source luntbuild/db/postgresql/luntbuild.sql;

6. How to backup a postgresql database
a, Backing up only database
#pg_dump -a -d -f
b. Dumping only database schema
#pg_dump -s -d -f
c. Complete database dump
#pg_dump -d -f

7. Taking full all databases:
$ pg_dumpall > db.out

8.How to restore the database,
$ psql -d databasename -f db.out -UUser

-Njoy

How do I install postgresql manually

hello,

As of most operating system comes along with their own package repositories with the stable version of each packages. So that we can easily install it either using apt or yum.

Standard installation of PostgreSQL.
1. # apt-get install postgresql* (Debain/Ubuntu)
2. # yum install postgresql* ( RHEL/Fedora/Centos)

Here I am listing manual installation steps of latest PostgreSQL from the Postgresql website.

1. Download the latest source code fro, postgreSQL site (http://www.postgresql.org/ftp/source/)
#wget http://wwwmaster.postgresql.org/download/mirrors-ftp/source/v8.4.1/postgresql-8.4.1.tar.gz

2. tar -zxvf postgresql-8.4.1.tar.gz
./configure
gmake
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su – postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

installing as service
1. go to /installation/startup-scripts
2. copy linux file to /etc/init.d/postgresql
3. chmod 755 /etc/init.d/postgresql
4, chkconfig –add postgresql
5, chkconfig postgresql on
6. #service postgresql start

Set /usr/local/pgsql/bin in system PATH

7. #PATH=$PATH:/usr/local/pgsql/bin;export PATH

We can’t execute or adminsitrate any command initialy even we have root access to the server. We need to add the the user ‘root’ as a super user in posgressql. For doing this,
#su – postgres
#create user root : Will ask for the role given, Just press yes to execute. Now you can administrate the posgresql from system root console.

8. Creating new database using #createdb
9. Create user using #createuser
10. Listing Databases #psql -l

#####################################

How to create access privileges to other hosts in the network.

Posgresql initially will not permitt any host to access the server remotely.For enabling remote access, we need to modify the certain settings on config. file. A. editing on /etc/postgressql.conf. B. pg_hba.conf

1. edit postgresql.conf which should be look like this
# – Connection Settings -
listen_addresses ='*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to ‘localhost’, ‘*’ = all
port = 5432

2. edit pg_hba.conf should look like this

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# “local” is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.0.1 255.255.255.0 trust
# I assume you have 162.0.0.0/24 network

Note that The last line which gives access permission to all of the given network ranges(192.168.0.x)

# service postgresql restart

How to test whether it is working or not ?

Just login to the other desktop on the same network and telnet to the ostgresql port.
# telnet 192.168.20.254 5432 if you got a window open means that it's listening.

Installing PhpPGadmin for posgres

1. Locate and unzip phpPgAdmin-4.0.1.tar.gz file to Apache web directory

2. Edit the file phpPgAdmin-4.0.1/conf/config.inc.php. find the entry and find the entry on the file and make the true to false
extra_login_security = true to false

which should look like this

$conf['$conf['extra_login_security'] = false;'] = false;

-Njoy

Memory inspection

A, Cheking  memory size and avialbale ususge

$free -m

B, What is the maximum RAM supported by the system?

$ dmidecode -t 16

C, How many memory slots are available for expansion?
$ dmidecode -t 17 | grep Size

D, Detailed memory information

$ cat /proc/meminfo

Useful PostgreSQL Commands

This scripts will create  postgres database with specified owner and resore a sql dump in to it

#The super user postgres  login along woth the password ‘admin’ in psql console

$psql  -h localhost -p 5432 <postgres> “admin”

#creating a database named ‘testdb’

postgres=# CREATE USER testuser;

postgres=# ALTER USER  testuser PASSWORD ‘letmein’;

#create a database named ‘mydb’ owned by the given user

postgres=# CREATE DATABASE mydb  WITH OWNER testuser;

#restoring dump to that database

postgres=# source<path/to/sql file>;

Some external commands 

1, Dumping  only data
$pg_dump -a -d <DB> -f <Filename.sql> -U <user> -p <passwd>

2, Dump only database schema

$pg_dump -s -d <DB> -f <Filename> -U <user> -p <passwd>
3, Complete   database dump

$pg_dump  -d <DB> -f <Filename>  -U <user> -p <passwd>

4,  To dump all databases

$ pg_dumpall > db.out

5, To reload this database use, for example:

$ psql -f db.out postgres

6, List the databases

$psql -l