dammytutorial

postgreSQL routine maintenance and proactive-maintanace.

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

postgreSQL routine maintenance and proactive-maintanace.

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

-$psql–U postgresedbstore
-edbstore=#

also see below

postgreSQL routine maintenance and proactive-maintanace.

postgreSQL Connecting to a Database

In order to connect to a database you need to know the name of your target database, the host name and port number of the server and the user name.
Use command line options, such as -d, -h, -p, and -U.

Syntax:

./psql -p 5444 -u <user> -d <db-name>

also see below

postgreSQL routine maintenance and proactive-maintanace.

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:

also see below

postgreSQL routine maintenance and proactive-maintanace.

postgreSQL - PSQL MATA Commands

PSQL META Command	Usage
\dt	List the table
\dn	List the sachems
\l	List the databases
\di	List of Indexes
\d <table-name>	Describe table(Columns and their type)
\db	List of table spaces(In which places tables are storing)
\du	List of users
\ds	List of system table
\dvs	List of system views
\l+	List of databases with size
\dt+	List of tables with size
\!	present working directory
\dp	Table space privileges
\q	Quit from psql shell
select current_user	To 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;

above query will prints process id of PostgreSQL

also see below

postgreSQL routine maintenance and proactive-maintanace.