How to build LAPP (Linux + Apache + Postgres + PHP) easy

This LAPP is to be built based on Ubuntu (or Debian or any derivative of the Ubuntu)

Install Postgresql

sudo apt-get install postgresql-8.1 php5-pgsql

Install Apache

• The following mod-security, ldap, and odbc libraries are optional:

sudo apt-get install apache2 libapache2-mod-php5 php5-gd
sudo apt-get install libapache2-mod-security php5-ldap php5-odbc

• Restart Apache

sudo /etc/init.d/apache2 restart




============== below are optional or if you know what to do please skip ============== 

Install other software (optional)

sudo apt-get install openssh-server unattended-upgrades
sudo apt-get install unzip zip aspell-en aspell-fr aspell-de aspell-es
sudo apt-get install curl php5-curl php5-xmlrpc
sudo apt-get install clamav-base clamav-freshclam clamav

Configure Postgres (if you know what to do please skip)

• Create the database user 'webuser'.

sudo -u postgres createuser -D -A -P webuser

Enter in a NewDatabasePassword here, then answer 'N' to the question.

• Create the database 'webdb' for the user 'webuser'. Enter the password that you just created.

sudo -u postgres createdb -E utf8 -O webuser webdb

• Secure the postgresql database with an admin password.

sudo -u postgres psql template1
# ALTER USER postgres WITH PASSWORD 'NewAdminDatabasePassword';
# \q

• Edit the file '/etc/postgresql/8.1/main/pg_hba.conf' and on line 79 change the words ident sameuser to md5.

sudo nano /etc/postgresql/8.1/main/pg_hba.conf

• Restart the database:

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

Useful PostgreSQL Commands

article source: http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/

Some of the open source application comes with postgreSQL database. To maintain those application, companies may not hire a fulltime postgreSQL DBA. Instead they may request the existing Oracle DBA, or Linux system administrator, or programmers to maintain the potgreSQL. In this article let discuss about the 15 practical postgresql database commands which will be useful to both DBA and expert psql users.

Also, refer to our previous article about 15 Practical PostgreSQL DBA Commands.

1. How to find the largest table in the postgreSQL database?

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;              relname              | relpages
-----------------------------------+----------
 pg_proc                           |       50
 pg_proc_proname_args_nsp_index    |       40
 pg_depend                         |       37
 pg_attribute                      |       30

If you want only the first biggest table in the postgres database then append the above query with limit as:

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1; relname | relpages
---------+----------
 pg_proc |       50
(1 row)

• relname – name of the relation/table.
• relpages - relation pages ( number of pages, by default a page is 8kb )
• pg_class – system table, which maintains the details of relations
• limit 1 – limits the output to display only one row.

2. How to calculate postgreSQL database size in disk ?

pg_database_size is the function which gives the size of mentioned database. It shows the size in bytes.

# SELECT pg_database_size('geekdb');pg_database_size
------------------
         63287944
(1 row)

If you want it to be shown pretty, then use pg_size_pretty function which converts the size in bytes to human understandable format.

# SELECT pg_size_pretty(pg_database_size('geekdb')); pg_size_pretty
----------------
 60 MB
(1 row)

3. How to calculate postgreSQL table size in disk ?

This is the total disk space size used by the mentioned table including index and toasted data. You may be interested in knowing only the size of the table excluding the index then use the following command.

# SELECT pg_size_pretty(pg_total_relation_size('big_table')); pg_size_pretty
----------------
 55 MB
(1 row)

How to find size of the postgreSQL table ( not including index ) ?

Use pg_relation_size instead of pg_total_relation_size as shown below.

# SELECT pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty
----------------
 38 MB
(1 row)

4. How to view the indexes of an existing postgreSQL table ?

Syntax: # \d table_name

As shown in the example below, at the end of the output you will have a section titled as indexes, if you have index in that table. In the example below, table pg_attribute has two btree indexes. By default postgres uses btree index as it good for most common situations.

test=# \d pg_attribute   Table "pg_catalog.pg_attribute"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 attrelid      | oid      | not null
 attname       | name     | not null
 atttypid      | oid      | not null
 attstattarget | integer  | not null
 attlen        | smallint | not null
 attnum        | smallint | not null
 attndims      | integer  | not null
 attcacheoff   | integer  | not null
 atttypmod     | integer  | not null
 attbyval      | boolean  | not null
 attstorage    | "char"   | not null
 attalign      | "char"   | not null
 attnotnull    | boolean  | not null
 atthasdef     | boolean  | not null
 attisdropped  | boolean  | not null
 attislocal    | boolean  | not null
 attinhcount   | integer  | not null
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

5. How to specify postgreSQL index type while creating a new index on a table ?

By default the indexes are created as btree. You can also specify the type of index during the create index statement as shown below.

Syntax: CREATE INDEX name ON table USING index_type (column);

# CREATE INDEX test_index ON numbers using hash (num);

6. How to work with postgreSQL transactions ?

How to start a transaction ?

# BEGIN -- start the transaction.

How to rollback or commit a postgreSQL transaction ?

All the operations performed after the BEGIN command will be committed to the postgreSQL database only you execute the commit command. Use rollback command to undo all the transactions before it is committed.

# ROLLBACK -- rollbacks the transaction.
# COMMIT -- commits the transaction.

7. How to view execution plan used by the postgreSQL for a SQL query ?

# EXPLAIN query;

8. How to display the plan by executing the query on the server side ?

This executes the query in the server side, thus does not shows the output to the user. But shows the plan in which it got executed.

# EXPLAIN ANALYZE query;

9. How to generate a series of numbers and insert it into a table ?

This inserts 1,2,3 to 1000 as thousand rows in the table numbers.

# INSERT INTO numbers (num) VALUES ( generate_series(1,1000));

10. How to count total number of rows in a postgreSQL table ?

This shows the total number of rows in the table.

# select count(*) from table;

Following example gives the total number of rows with a specific column value is not null.

# select count(col_name) from table;

Following example displays the distinct number of rows for the specified column value.

# select count(distinct col_name) from table;

11. How can I get the second maximum value of a column in the table ?

First maximum value of a column

# select max(col_name) from table;

Second maximum value of a column

# SELECT MAX(num) from number_table where num  < ( select MAX(num) from number_table );

12. How can I get the second minimum value of a column in the table ?

First minimum value of a column

# select min(col_name) from table;

Second minimum value of a column

# SELECT MIN(num) from number_table where num > ( select MIN(num) from number_table );

13. How to view the basic available datatypes in postgreSQL ?

Below is the partial output that displays available basic datatypes and it’s size.

test=# SELECT typname,typlen from pg_type where typtype='b';
    typname     | typlen
----------------+--------
 bool           |      1
 bytea          |     -1
 char           |      1
 name           |     64
 int8           |      8
 int2           |      2
 int2vector     |     -1

• typname – name of the datatype
• typlen – length of the datatype

14. How to redirect the output of postgreSQL query to a file?

# \o output_file
# SELECT * FROM pg_class;

The output of the query will be redirected to the “output_file”. After the redirection is enabled, the select command will not display the output in the stdout. To enable the output to the stdout again, execute the \o without any argument as mentioned below.

# \o

As explained in our earlier article, you can also backup and restore postgreSQL database using pg_dump and psql.

15. Storing the password after encryption.

PostgreSQL database can encrypt the data using the crypt command as shown below. This can be used to store your custom application username and password in a custom table.

# SELECT crypt ( 'sathiya', gen_salt('md5') );

PostgreSQL crypt function Issue:

The postgreSQL crypt command may not work on your environment and display the following error message.

ERROR:  function gen_salt("unknown") does not exist
HINT:  No function matches the given name and argument types.
         You may need to add explicit type casts.

PostgreSQL crypt function Solution:

To solve this problem, installl the postgresql-contrib-your-version package and execute the following command in the postgreSQL prompt.

# \i /usr/share/postgresql/8.1/contrib/pgcrypto.sql

How to recover postgres database from postgres data folder

If Windows crashed by some reason you can't get into the system, then if you have to reinstall Windows or upgrade to a new hard drive. As long as the Postgres data folder still there then you are lucky to get your database back.

1) install the same version postgres you had on the old system before, say postgres8.3;

2) stop the postgres service from the service form or use the command below:
    net stop pgsql-8.3

3) open a CMD, then type in:
    runas   /user:postgres   cmd
    The above command will open a command window as postgres role, it will ask for the postgres service password to get the access. Please note here that is not postgres user password;

4) cd into postgres folder:
    c:\program files\postgres\

5) delete everything under the data folder;

6) then copy the old data folder(say from U drive) cross, type in the command:
    xcopy /H /E /I U:\program files\postgres\data C:\program files\postgres\data

7) restart postgres services from the service form or use the command below:
    net start pgsql-8.3

After that, you should be able to log in to use your postgres database as before

How to remove postgres user from Windows

open CMD by administrator mode, then type in:

net user postgres /delete

If you just want to change user postgres' password to 'postgres', then type in:

net user postgres postgres

How to set Postgresql money type to use 3 decimal places in Windows

LINUX
=====
Under Linux, type command in postgres database:

set lc_monetary to 'ar_BH.utf8';

ar_BH represents Arabic (Bahrain) locale, the above command change the locale monetary setting on-the-fly in postgresql database, the let do a test:

select 34.888::text::money;

You will get 34.888 with 3 decimal places money type. Tested in Postgresql 8.4.6.

WINDOWS
=======
However the above set command will get an error message under Windows.

FATAL: invalid value for parameter “lc_monetary”

You will need to:

set lc_monetary to "Arabic, Bahrain";

This will set the lc_monetary to arabic locale but only display 2 decimal places by default, it seems everything right of the comma is ignored.

To workaround this, use an '_' in plase of ', ' like:

set lc_monetary to "Arabic_Bahrain";

Tested with Windows XP + PostgreSQL 9.0.3(this solution should work for PostgreSQL 8.3 or later version too)

Thanks Jasen who get this resolved, more information please see his post @ postgresql forum:
http://postgresql.1045698.n5.nabble.com/win-Locales-quot-Arabic-Gum-quot-td3369213.html

Outputting from Postgres to CSV

\f ','
\a
\t
\o /home/john/moocow.csv
SELECT foo,bar FROM whatever;
\o
\q

If a field has newlines, this will break. You can do something like this instead.....

SELECT foo, bar, '"' || REPLACE(REPLACE(field_with_newilne, '\n', '\\n'), '"', '""') || '"'

FROM whatever;

Then you can import the data into another database if needed:

copy whatever(foo,bar) FROM
'/home/john/moocow.csv' DELIMITER ','
CSV HEADER; 

change postgresql time zone for west Australia

In west Australia, like Perth, when you are using postgresql database, you may need to change the time zone abbreviations to Australia:

change it for a session

postgres=# set timezone_abbreviations to 'Australia';

change it for database

postgres=# alter database DATABASENAME set timezone_abbreviations to 'Australia';

psql: FATAL: Ident authentication failed for user "username" Error and Solution

Q. I've installed Postgresql under Red Hat Enterprise Linux 5.x server. I've created username / password and database. But when I try to connect it via PHP or psql using following syntax:

psql -d myDb -U username -W

It gives me an error that read as follows:

psql: FATAL: Ident authentication failed for user "username"

How do I fix this error?

A. To fix this error open PostgreSQL client authentication configuration file /var/lib/pgsql/data/pg_hba.conf :
# vi /var/lib/pgsql/data/pg_hba.conf
This file controls:

1. Which hosts are allowed to connect
2. How clients are authenticated
3. Which PostgreSQL user names they can use
4. Which databases they can access

By default Postgresql uses IDENT-based authentication. All you have to do is allow username and password based authentication for your network or webserver. IDENT will never allow you to login via -U and -W options. Append following to allow login via localhost only:

local all all trust
host all 127.0.0.1/32 trust

Save and close the file. Restart Postgresql server:
# service postgresql restart
Now, you should able to login using following command:
$ psql -d myDb -U username -W


article source: http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/ 


Regular Expressions in PostgreSQL

Every programmer should embrace and use regular expressions (INCLUDING Database programmers). There are many places where regular expressions can be used to reduce a 20 line piece of code into a 1 liner. Why write 20 lines of code when you can write 1.

Regular expressions are a domain language just like SQL. Just like SQL they are embedded in many places. You have them in your program editor. You see it in sed, grep, perl, PHP, Python, VB.NET, C#, in ASP.NET validators and javascript for checking correctness of input. You have them in PostgreSQL as well where you can use them in SQL statements, domain definitions and check constraints. You can mix regular expressions with SQL. When you mix the two domain languages, you can do enchanting things with a flip of a wrist that would amaze your less informed friends. Embrace the power of domain languages and mix it up. PostgreSQL makes that much easier than any other DBMS we can think of.
For more details on using regular expressions in PostgreSQL, check out the manual pages Pattern Matching in PostgreSQL
The problem with regular expressions is that they are slightly different depending on what language environment you are running them in. Different enough to be frustrating. We'll just focus on their use in PostgreSQL, though these lessons are applicable to other environments.

Common usages

We are going to go backwards a bit. We will start with demonstrations of PostgreSQL SQL statements that find and replace things and list things out with regular expressions. For these exercises we will be using a contrived table called notes, which you can create with the following code.

 CREATE TABLE notes(note_id serial primary key, description text);
 INSERT INTO notes(description)
   VALUES('John''s email address is johnny@johnnydoessql.com.  Priscilla manages the http://www.johnnydoessql.com site.  
    She also manages the site http://jilldoessql.com and can be reached at 345.678.9999
    She can be reached at (123) 456-7890 and her email address is prissy@johnnydoessql.com or prissy@jilldoessql.com.');
         
 INSERT INTO notes(description)
   VALUES('I like ` # marks and other stuff that annoys militantdba@johnnydoessql.com.  
    Militant if you have issues, give someone who gives a damn a call at (999) 666-6666.');

Regular Expressions in PostgreSQL

PostgreSQL has a rich set of functions and operators for working with regular expressions. The ones we commonly use are ~, regexp_replace, and regexp_matches.
We use the PostgreSQL g flag in our use more often than not. The g flag is the greedy flag that returns, replaces all occurrences of the pattern. If you leave the flag out, only the first occurrence is replaced or returned in the regexp_replace, regexp_matches constructs. The ~ operator is like the LIKE statement, but for regular expressions.

Destroying information

The power of databases is not only do they allow you to store/retrieve information quickly, but they allow you to destroy information just as quickly. Every database programmer should be versed in the art of information destruction.

 -- remove email addresses if description has email address 
 UPDATE notes SET description = regexp_replace(description, 
       E'[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,4}', 
        '---', 'g') 
        WHERE description 
        ~ E'[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,4}'; 
        
 -- remove website urls if description has website urls 
 -- matches things like http://www.something.com or http://something.com

 UPDATE notes SET description = regexp_replace(description, 
       E'http://[[[:alnum:]]+.]*[[:alnum:]]+[.][[:alnum:]]+', 
        E'--', 'g') 
        WHERE description 
        ~ E'http://[[[:alnum:]]+.]*[[:alnum:]]+[.][[:alnum:]]+'; 

-- remove phone numbers if description 
-- has phone numbers e.g. (123) 456-7890 or 456-7890 or 123.456.7890
UPDATE notes SET description = regexp_replace(description, 
      E'[\(]{0,1}[0-9]{3}[\).-]{0,1}[[:space:]]*[0-9]{3}[.-]{0,1}[0-9]{4}',
        '---', 'g') 
        WHERE description 
        ~ E'[\(]{0,1}[0-9]{3}[\).-]{0,1}[[:space:]]*[0-9]{3}[.-]{0,1}[0-9]{4}'; 

-- set anything to single space that is not not a \ ( ) & * /,;. > < space or alpha numeric        
UPDATE notes set description = regexp_replace(description, E'[^\(\)\&\/,;\*\:.\>\<[:space:]a-zA-Z0-9-]', ' ') 
  WHERE description ~ E'[^\(\)\&\/,;\*\:.\<\>[:space:]a-zA-Z0-9-]';
  
-- replace high byte characters with space 
-- this is useful if you have your database in utf8 and you often need to use latin1 encoding
-- and you have a table that shouldn't have high byte characters 
-- such as junk you get from scraping websites
-- high byte characters don't convert down to latin1
UPDATE notes SET description = regexp_replace(description,E'[^\x01-\x7E]', ' ', 'g')
WHERE description ~ E'[^\x01-\x7E]';

Getting list of matches

These examples use similar to our destroy but show us in a table, a list of stuff that match. Here we use our favorite PostgreSQL regexp_matches function.

-- list first email address from each note --
SELECT note_id, 
  (regexp_matches(description, 
    E'[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+'))[1]  As email
FROM notes
WHERE description ~ E'[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+'
ORDER By note_id, email;
-- result
 note_id |             email
---------+-------------------------------
       1 | johnny@johnnydoessql.com
       2 | militantdba@johnnydoessql.com


--list all email addresses
-- note this uses the PostgreSQL 8.4 unnest construct 
-- to convert the array returned to a table
SELECT note_id, 
  unnest(
    regexp_matches(description, 
    E'[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+', 'g')
  ) As email
FROM notes
WHERE description ~ E'[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+'
ORDER By note_id, email;

-- returns
 note_id |             email
---------+-------------------------------
       1 | johnny@johnnydoessql.com
       1 | prissy@jilldoessql.com
       1 | prissy@johnnydoessql.com
       2 | militantdba@johnnydoessql.com

Parts of a Regular Expresssion

Here we will just cover what we consider the parts you need to know if you don't have the patience or memory to remember more. Regular expressions are much richer than our simplified view of things. There is the great backreferencing feature we won't get into which allows you to reference an expression and use it as part of your replacement expression.

Part	Example
Classes []	Regular expression classes are a set of characters that you can treat as interchangeable. They are formed by enclosing the characters in a bracket. They can also have nested classes. For example [A-Za-z] will match any letter between A-Z and a-z. [A-Za-z[:space:]] will match those plus white spaces in PostgreSQL. If you need to match a regular expression character such as ( then you escape it with \. so [A-Za-z\(\)] will match A thru Z a thru z and ( ). Classes can contain other classes and expressions as members.
.	The famous . matches any character. So the infamous .* means one or more of anything.
Quantity {} + *	You denote quantities with {}, +, * + means 1 or more. * means 0 or more and {} to denote allowed quantity ranges. [A-Za-z]{1,5} means you can have between 1 and 5 alpha characters in and expression for it to be a match.
()	This is how you denote a subexpression. A subexpression can be composed of multiple classes etc and can be backreferenced. They define a specific sequence of characters.
[^members here]	This is the NOT operator in regular expressions so for example [^A-Za-z] will match any character that is not in the alphabet.
Special classes	[[:alnum:]] any alphanumeric, [[:space:]] any white space character. There are others, but those are the most commonly used.

 Article source: http://www.postgresonline.com/journal/index.php?/archives/152-Regular-Expressions-in-PostgreSQL.html