How to Create Database dump Backup and restore postgresql

To create dump file as a backup from postgresql follow the simple commands below:
pg_dump is an effective tool to backup postgres database. It creates a *.sql file with CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. To restore these dumps psql command is enough.
 
1: login to your server through ssh
and run the commands:

2: pg_dump -U {user-name} {source_db} -f > {dumpfilename.sql}
In my case it will be 
    pg_dump -U shahmeer firstdb > firstdb.sql

Backup all postgres databases
To backup all databases, list out all the available databases;
login as postgres /psql user:
# su - postgres
#psql -l
will show you list of the databases;
\q
run the command to backup all the databases:
#pg_dumpall > alldbs.sql
now as the backup completed you have to verify the backup;
# grep "^[\]connect" alldbs.sql
\connect db1
\connect db2
\connect db3...


3: Backup a single table from specific database:
# pg_dump --table contacts -U shahmeer -d firstdb -f conatactstable.sql

How To Restore Postgres Database;

1: Restore postgres database
# psql -U shahmeer firstdb.sql < newdb
if you are going to restore the local database on the remote server then use the below command
#pg_dump firstdb.sql | psql -h hostname dbname

3: Restore All the postgres databases
#su postgres
#psql -f alldb.sql
4: Restore a single postgres table
#psql -f contacts.sql newdb
where new db is the database name and contacts.sql is a dump of table contacts

5:To create a dump of two tables in a single command:
# pg_dump -t table1, -t table2 -U firstdb > 2tables.sql

Thats it, if you have any query regarding this post or if facing any issue please comments here.

How to Postgres Create Datase, User Commands

These are some basic steps to follow for newbie how to use postgres database!
1:- Create database:

First install the postgresql on your server or locally.
Then follow the below steps to create the Database!
by default there is no database in postgres so you have to user the default user.

You need to login as database super user under postgresql server. Again the simplest way to connect as the postgres user is to change to the postgres unix user on the database server using su command as follows:
su - postgress

insert the command
#psql template1

tamplat1#

2:Add user shahmeer
template1# CREATE USER shahmeer WITH PASSWORD 'helloE^$(0~E';

3: Create Database firstdb:
template1=# CREATE DATABASE firstdb;

Now grant all privileges on database

template1=# GRANT ALL PRIVIGES ON DATABASE firstdb to shahmeer;

Type \q to quit:

template1# \q 
/////////////////////////////////////////////////////////////////////////////

Test user shahmeer login:

#psql -U shahmeer -d firstdb

firstdb> \l;

will show all the databases in postgresql:

........................................................................................

How to use Sphinx with Postgresql on ubuntu 10.10

1 createdb -U postgres test
2  psql -U postgres test
3 test=# create table test (id integer, text text);
4 insert into test (text) values ('This is a test.');
  insert into test (text) values ('I have another thing to test.');
  -- A user with a password is required.
create user test with password 'test';
alter table test owner to test;
\q
.........................................................................
Configure sphinx (replace vim with your editor of choice):

$ cd /usr/local/etc
$ sudo cp sphinx-min.conf.dist sphinx.conf
$ sudo vim sphinx.conf
 
These values worked for me. I left configuration for indexer and searchd unchanged:
source src1
{
  type = pgsql
  sql_host = localhost
  sql_user = test
  sql_pass = test
  sql_db = test
  sql_port = 5432
  sql_query = select id, text from test
  sql_query_info = SELECT * from test WHERE id=$id
}
 Index test1
{
  source = src1
  path = /var/data/test1
  docinfo = extern
  charset_type = utf-8 
} 




indexer
{
        mem_limit                               = 32M
}
 
searchd
{
        port                                    = 5432
        log                                     = /var/log/searchd.log
        query_log                               = /var/log/query.log
        read_timeout                    = 5
        max_children                    = 30
        pid_file                                = /var/log/searchd.pid
        max_matches                             = 1000
        seamless_rotate                 = 1
        preopen_indexes                 = 0
        unlink_old                              = 1
}

Reindex:
 Now go to terminal and run the below commands
$ sudo mkdir /var/data
$ sudo indexer --all


if it create some error of SELECT in line 16 in sphinx.conf then go to the and comment the line because the terminal only support the info_query. 
It will create files in /var/data




Run searchd:
$ searchd

Play:
$ search world
Sphinx 0.9.9-release (r2117)
Copyright (c) 2001-2009, Andrew Aksyonoff

using config file '/usr/local/etc/sphinx.conf'...
index 'test1': query 'world ': returned 1 matches of 1 total in 0.000 sec
words:
1. 'world': 1 documents, 1 hits

Displaying matches:
1. document=1, weight=1
 To reindex your database after inserting more data. run the command

indexer --rotate --all
it reindex your database.

This is all about the Postgresql with Phinx, for mysql you will have alot of materials on web.

Hope it will help.
Regards:
imran shahmeer

how to install Sphinx on Ubuntu 10.10 with PostgreSQL

Sphinx is a full-text search engine, publicly distributed under GPL version 2. Commercial licensing (eg. for embedded use) is available upon request. You can use Sphinx for creating big data indexes. It supports MySQL, PostgreSQL, ODBC.

If you use Ubuntu 10.10, you can use

1: go to sphinx site and download the stable version.

2: install the dependency g++ c compiler.

apt-get install gcc 

3: now do the following steps

4: cd to directory where you download the sphinx and type the command:

mv sphinx.tar.gz(the name of file you downloaded) /usr/local

5: cd /usr/local/

6: tar zxvf sphinx.tar.gz and press enter

7: cd sphinx 

8: ./configure --with-postgres /

may its give an error message:

ERROR: cannot find PostgreSQL libraries. If you want to compile with PosgregSQL support,

       you must either specify file locations explicitly using

       --with-pgsql-includes and --with-pgsql-libs options, or make sure path to

       pg_config is listed in your PATH environment variable. If you want to

       disable PostgreSQL support, use --without-pgsql option.

locate your pg_config with command # whereis pg_config this will show you the if it is installed there. if just show 

pg_config:

then install the library;

9: sudo apt-get install libpq-dev

now again type # whereis pg_config

 pg_config:/usr/bin/pg_config /usr/share/man/man1/pg_config.1.gz

now again type the command 

# ./configure --with-postgres /usr/bin/pg_config  and press enter

configuration done

------------------

You can now run 'make' to build Sphinx binaries,

and then run 'make install' to install them.

10: Build the binaries:

# make

11: install the package

#make install 

if it successfully leave the directory it mean you have install it. 

.......................................................................................................................

for any query regarding installation leave a comments here. or you can email me at shahmeer09@gmail.com

Backup your system through perl scripts using cron job

1: create a perl script that can take a backup of your system. the procedure is as follows:

1.a: create a file with name of mirror.pl and past the below code to that file 

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

#!/usr/bin/env perl

#!/usr/bin/perl

# Allow us to copy files correctly.

use File::Copy;

# User-configurable.  Don't include a / at the end of the path.

#  Source = Base directory to start the backup from

#  Dest = Base directory to backup to

$Source = "/home";

$Dest = "/media/9b968b0b-ebef-49ac-90c6-ad673d426bc6/home/pfserver/BackUp/pfserver/cron";

$Reverse = 0;

if ($Reverse)

{

    $Temp = $Source;

    $Source = $Dest;

    $Dest = $Temp;

}

# Global variable.  Turn on Verbose Mode by using "mirror.pl -v"

$Verbose = 0;

# Define what you do not want copied over.  It is case sensitive.

# If you want a specific directory not copied, such as the "/logs/"

# directory, use "^/logs/" to specify that directory.  It uses a regexp

# to see if the directory which is about to be copied should be excluded.

# If you do not specify the ^ in front, such as the "/_themes/" directory,

# it will exclude all _themes directories it comes across.

# You can specify a file by using "^/dir/dir/file.txt" or all files with

# a particular name by "/file.txt$" and making sure that $ is there so

# it matches to end of line.

#@Excludes = ("^/backup/",

#        "^/ftp/",

#        "^/Images/",

#        "^/logs/",

# "^/publish/",

# "^/recycled/", "^/RECYCLED/",

# "^/recycler/", "^/RECYCLER/",

# "/_borders/", "/_BORDERS",

# "/_derived/", "/_DERIVED/",

# "/_fpclass/", "/_FPCLASS/",

# "/_overlay/", "/_OVERLAY/",

# "/_private/", "/_PRIVATE/",

# "/_themes/", "/_THEMES/",

# "/_vti_bin/", "/_VTI_BIN/",

# "/_vti_cnf/", "/_VTI_CNF/",

# "/_vti_log/", "/_VTI_LOG/",

# "/_vti_pvt/", "/_VTI_PVT/",

# "/_vti_txt/", "/_VTI_TXT/");

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

# End of user-configurable section #

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

if ($ARGV[0] eq "-v")

{

    print "Verbose mode ON!\n";

    $Verbose = 1;

}

PerformBackup("/");

# End of the main driver, proceed directly to functions.

# Gets a listing of files.  Returns the array.

sub GetFiles {

    local ($Dir) = @_;

    local (@Files);

    opendir(DIR, $Dir);

    @Files = readdir(DIR);

    closedir(DIR);

    return @Files;

}

# Checks to see if the file/directory name passed in should be

# excluded in the backup

sub IsExcluded {

    local ($File) = @_;

    foreach (@Excludes)

    {

if ($File =~ /$_/)

  {

   print "Excluding $File\n" if ($Verbose);

   return 1;

}

    }

    return 0;

}

# Compares/copies files

sub BackupFile {

    local ($File) = @_;

    # Only copy if newer or different sized from what I have

    if (-e "$Dest$File")

    {

        if (-M "$Source$File" >= -M "$Dest$File")

        {

   if (-s "$Source$File" == -s "$Dest$File")

   {

       return;

   }

        }

    }

    # Exclude files which can be excluded

    if (IsExcluded($File))

    {

return;

    }

    print "        $File\n" if ($Verbose);

    copy("$Source$File", "$Dest$File");

}

# Performs the backup on the specified directory, recursively calling

# itself for each subdirectory which does not get excluded.

sub PerformBackup {

    local ($Dir) = @_;

    local (@SFiles, @DFiles, @Dirs, $File);

    print "$Dir\n" if ($Verbose);

    # Get the file lists

    @SFiles = GetFiles("$Source$Dir");

    @DFiles = GetFiles("$Dest$Dir");

    # For each file, possibly copy.

    # For each directory, save list of directories for later.

    foreach (sort(@SFiles))

    {

        # Ignore ".", "..", and hidden files

        next if (/^\./);

        $File = $_;

        

        # If it is a directory, save it for later.

        # Else, possibly copy now.

        if (-d "$Source$Dir$File")

{

   push(@Dirs, $File);

}

else

        {

   BackupFile("$Dir$File");

        }

    }

    

    # Process the directories

    foreach (sort(@Dirs))

    {

        $File = "$Dir$_/";

next if (IsExcluded($File));

        if (! -d "$Dest$File")

        {

            mkdir("$Dest$File", 0755);

        }

        PerformBackup("$File");

    }

}

save and exit

now copy this script to /usr/sbin/

cp mirror.pl /usr/sbin/

enter the commands

chmod +x /usr/sbin/mirror.pl

open your crontab

 env EDITOR=vim crontab -e

and the entries to cron for backup to run

38 10 * * * /usr/sbin/mirror.pl 

thats it. enjoy the backup .

if you do want to check your perl cron is running 

open terminal and type top command it will show you that perl is running through cron but the time should be set proper. mean you have to check it 10:40 round about. 

Regards: 

Imran shahmeer