Wednesday, 13 July 2016

How to increase Query Performance

How to get fastness on query? How to get results quickly?
By inceasing work_mem we can acheive fastness on query results
show work_mem;
set work_mem to 1000MB

Increasing work mem to specific user
Alter user <user> set work_mem to '1000MB'

How to increase read performance?

By making below changes in config file we can achieve better read performance

fsynce=false
increase work_mem
increase shared_bufferes
create indexs

How to increase write performance?

By making below changes in config file we can achieve better write performance

decrease checkpoint_timeout
increase shared_buffers
increase wal_buffers
increase work_mem
at No comments:
Labels:

postgreSQL How to start server and stop server?

How to stop postgreSQL?

./pg_ctl -D ../data stop

How to force stop postgreSQL?
./pg_ctl -D ../data stop -m f --->force stop

How to start postgreSQL?
./pg_ctl -D ../data start

How to restart postgreSQL?
./pg_ctl -D ../data restart

Note: In above samples pg_ctl is a executable which can be found under bin directory. -D stands for directory and ../data is data directory.


Security :
pg_hdb.cfg configuration file will have all clients IP address non listed clients cannot access.
pg_hba.conf can be find under data directory not under bin directory.another config file postgres.cfg where will do postgreSQL configurations.

How to load configuration file without restart with query?
Answer: select pg_reload_conf();

How to rotate db logs?
Answer: select pg_rotate_logfile();

How to view log file location?
Answer: select pg_ls_dir('pg_log');

How to view system tables?
Answer: \d$
also see below

at 2 comments:
Labels:

postgreSQL Tablespaces and datafiles

  • Each database has a separate subdirectory.
  • Tablespaces can be created using the CREATE TABLESPACE command.
  • The Tablespace directory must be created and the appropriate permissions must be set prior to running the CREATE TABLESPACE command.

Syntax:
    CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'

  Use tablespace:

  Use the TABLESPACE keyword while creating tables and indexes to specify a   tablespace name.
  Syntax: 
            TABLESPACE <table space name>
  • default_tablespace server parameter sets default tablespace.
  • default_tablespace parameter can also be set using the SET command at the session level.temp_tablespaces parameter determines the placement of temporary tables and indexes and temporary files.
  • temp_tablespaces can be a list of tablespace names.
Example:
show default_tablespace;
show temp_tablespaces;
How to set default table space?

  • set default_tablespace <table-space>
  • Alter tablespace name rename to new_name
  • Alter tablespace name owenr to new_owner
 Drop table space:
drop tablespace <name>

create database in different table space:
create databse <db-name> tablespace="<table-space-name>
create database <db-name> <table-space-name>

create table space with location:
at No comments:
Labels:

postgreSQL Grant OR Revoke user Privileges

Cluster level:

  • Granted to a user during CREATE or later using ALTER USER
    These privileges are granted by superuser

Object Level:

  • Granted to user using GRANTcommand
  • These privileges allow a user to perform particular action on an database object, such as table, view, sequence etc.
  • Can be granted by Owner, superuser or someone who have been given permission to grant privilege (WITH GRANT OPTION)

Grant statement:

  • GRANT can be used for granting object level privileges to database users, groups or roles.
  • Privileges can be granted on a tablespace, database, schema, table, sequence, domain and function.
  • GRANT is also used to grant a role to a user.
Syntax:
-Type \h GRANT in edb-psqlterminal to view the entire syntax and available privileges that can be granted on different objects


Grant commad

Revoke statement:

  • REVOKE can be used for revoking object level privileges to database users, groups or roles.
  • Privileges can be revoked on a tablespace, database, schema, table, sequence, domain and function.
  • REVOKE [ GRANT OPTION FOR ] can be used to revoke only the grant option without revoking the actual privilege.
at No comments:
Labels:

How to create user in postgreSQL?

  • Database users are completely separate from operating system users.
  • Database users are global across a database cluster.
  • Username must be unique and require authentication.
  • Every connection made to a database is made using a user.
  • enterprisedbis a predefined superuserin default data cluster.
  • Pre-defined superusername can be specified during initialization of the database cluster.
  • This pre-defined superuserhave all the privileges with GRANT OPTION.
create user thourg commad
The create user utility can also be used to create a user
Syntax:
create user[OPTION]... [ROLENAME]
  • Use --help option to view the full list of options available.
Ex:  create user --help
also see below
at No comments:
Labels:

postgreSQL Create and access Database

Create Database:

Createdb is a program(command) that executes from the shell to create new databases.
Example: createdb edbstore

  • Create Database command can be used to create a database in a cluster.
Syntax:
Creating Databases
CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ TABLESPACE [=] tablespace][ CONNECTION LIMIT [=] connlimit ] ]

Access Database:

The psqltool allows you to interactively enter, edit, and execute SQL commands.
The PEM Client tool can also be used to access a database.
To use psql to access a database:
    -Open the command prompt or terminal.
    -If PATH is not set you can execute next command from the bin directory location of              postgresinstallation

at No comments:
Labels:

postgreSQL Connecting to a Database

at No comments:
Labels:
Subscribe to: Posts (Atom)