Search This Blog

29 November 2010

Important PSql commands

1. Command to connect to postgres
  • psql -h localhost -U postgres
2. Few general command that required when connected to Postges though shell
  • \h for help with SQL commands
  • \? for help on internal slash commands
  • \g or terminate with semicolon to execute query
  • \q to quit
3. Command to view the database assign to this user
  • \l to List databases
  • SELECT datname FROM pg_database;
4. Command to connect to a database
  • \c database-name  
5. Command to list tables in current database
  • \d to List tables in database
  • SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'
6. Command to describe a table structure
  • \d table-name

Important Linux commands for PostgreSQL

  • createdb
    createdb tmpdb -h localhost -U postgres

    Create a new PostgreSQL database 
  • createuser
    createuser ekta –s -h localhost -U postgres –P

    Create a new PostgreSQL user. Above command will create a new user ekta with the permission of superuser (-s) . And –P will prompt you for the password for new user. If we write this command without –P, it will create used which will not require password authentication
  • dropdb
    dropdb tmpdb -h localhost -U postgres

    Remove an existing PostgreSQL database
  • dropuser
    dropuser ekta -h localhost -U postgres

    Drops (removes) a PostgreSQL user
  • pg_dump
    pg_dump -h localhost -U postgres tmpdb > tmpdb.sql
    pg_dump --insert -h localhost -U postgres tmpdb > tmpdb.sql

    All above command are used to take database dump. It will extract a PostgreSQL database into a script file. --insert option is used to dump data as insert command.
  • pg_dumpall
    pg_ dumpall -h localhost -U postgres > tmpdb.sql

    Extract all PostgreSQL databases into a script file
  • psql
    psql -h localhost -U postgres

    Open PostgreSQL interactive terminal
  • vacuumdb
    vacuumdb tmpdb -h localhost -U postgres

    Clean and analyze a PostgreSQL database
Note : For information on above command check Linux help using man command, for example man pg_dump

17 November 2010

Windows Apache PHP and Postgres (WAPP Server)

BitNami WAPPStack includes Apache 2.2.16, PostgreSQL 9.0.0-1, PHP 5.2.14 and phpPgAdmin 4.2.2. phpPgAdmin is a web interface for administration of PostgreSQL. Steps to install and configure WAPP Server are given below,

Installation
  •  Download free copy of WAPP Server from http://bitnami.org/stack/wappstack
  •  Locate the downloaded file and double click the file to launch the installation.
  • The WAPP Setup Wizard will appear. Click Next.
  • At the Select Destination Location you can change the install directory. The default is C:\Program Files\BitNami WAPPStack. We will use the default directory. Click Next.
  • Then you will be asked for PostgreSQL postgres user password. Enter the Password. A Postgres user account will be created with user name “postgres” and the password you have entered. This information will be then used to connect with PostgreSQL server, through command prompt or through phpPgAdmin. Click Next
  • The default listening port for Apache is 80 and for PostgreSQL is 5432. If those ports are already in use by other applications, you will be prompted for alternate ports to use. Enter the alternate port if prompted. Click next
  • You are now ready to begin the installation, screen will appear. Click next
  • When installation is complete, “Completing the BitNami WAPP Stack Setup Wizard” page will appear. Click on Finish
  • You can now launch your browser with http://localhost/
Folder to create PHP files
  • The installation process will create several subfolders under the main installation directory i.e. C:\Program Files\BitNami WAPPStack
  • Create your PHP files in C:\Program Files\BitNami WAPPStack\apache2\htdocs
How to access phpPgAdmin
  • You can access your phpPgAdmin by opening a browser and typing http://127.0.0.1/phppgadmin. You will be asked for a username and password. As user name, use "administrator" and as password use the value specified during installation
  • Once you are connected with phpPgAdmin, to connect with default Postgres database, click on the Postgres on left bar, it will again ask for database user name and password. As the user name, use “postgres” and as password use the value specified during installation