Wednesday 13 July 2016

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:

postgreSQL what is schema?

Schema is nothing but user ownes 
  • Tables
  • Views
  • Sequences
  • Functions
  • Domains
  • Packages
  • Procedures
  • Synonyms
  • Database Links
  • Procedures

Benefits of Schems:

  • A database can contains one or more named sachems.
  • By default, all databases contain a public schema.
There are several reasons why one might want to use sachems:
  • To allow many users to use one database without interfering with each other.
  • To organize database objects into logical groups to make them more manageable.
  • Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.

Schema search path:

  • Qualified names are tedious to write, so we use table names directly in queries.
  • If no schema name is given, the schema search path determines which schemas are searched for matching table names
Example:
SELECT * FROM employee
This statement will find the first employee table from the schemas listed in the search path.

  • The first schema named in the search path is called the current schema if that named schema exists.
  • Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.
  • To show the current search path, use the following command:
            SHOW search_path;
  • To put our new schema in the path, we use:
        SET search_path TO myschema, public;
  • The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored.
  • The second element refers to the public schema that we have seen already. To put our new schema in the path, we use:

SET search_path TO myschema, public;

Default Schema:
postgres=# show search_path;
search_path
----------------
"$user",public
(1 row)
postgres=#

To put our new schema in the path, we use:
SET search_path TO myschema, public;

Example:

postgres=# set search_path TO myschema,public;
SET
postgres=# create table cust(id integer, name varchar(10));
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
----------+--------------------------+----------+----------
myschema | cust | table | postgres
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(3 rows)
postgres=# set search_path to "$user",public;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+----------
public | consultant | table | postgres
public | consultant_id_sequence | sequence | postgres
(2 rows)
also see below
at No comments:
Labels:

postgreSQL - PSQL MATA Commands


PSQL META CommandUsage
\dtList the table
\dnList the sachems
\lList the databases
\diList of Indexes
\d <table-name>Describe table(Columns and their type)
\dbList of table spaces(In which places tables are storing)
\duList of users
\dsList of system table
\dvsList of system views
\l+List of databases with size
\dt+List of tables with size
\!present working directory
\dpTable space privileges 
\qQuit from psql shell
select current_userTo view current user
How to switch to database/user?
\c <db> <user>

how to create database outside?
createdb -p 5444 <db-name> -u <user>

Note: we can refer for port in postgres.conf

How to write Query results in to file?

\o <path>
select * from <table-name>;
\o --->switching off

How to importing script?
\i <path of .sql file>

How to print time taken by query?
\timing
-->time take to run particular query
Query
\timtig -->turn off

How to write history of commands into file?
\s <path>

Alternate for \dt+

select pg_relation_size('<table-name>')
    output in bytes

select pg_size_pretty(pg_relation_size('<table-name>'))
    output in MBs or in GB

select pg_size_pretty(pg_database_size('<database-name>'))
    output in MBs or in GB

select pid from pg_stat_activity;
at No comments:
Labels:
Subscribe to: Posts (Atom)