Nov 11 2016

PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE

Category: AdministrationFatih Acar @ 11:03

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4.

Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g. by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade supports upgrades from 8.4.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.

Source : postgresql.org

Postgres_logo

Steps Of Upgrade

  • Install PostgreSQL new release packages
  • Create new directory for 9.6
  • Initialize 9.6 version in 9.6 directory
  • Configure pg_hba.conf and postgresql.conf regarding to changed parameters of 9.5 version
  • Stop 9.5 PostgreSQL service
  • Start pg_upgrade
  • Change PostgreSQL service name and start PostgreSQL 9.6

System Information

OS : Centos 7 x64
PG : 9.5
DIR : /pgdata/9.5/data/
SERVICE : postgresql.service

Install PostgreSQL new release packages

[root@testdb /tmp]# yum install postgresql96-libs.x86_64 postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-contrib.x86_64 postgresql96-devel.x86_64 postgresql96-python.x86_64 postgresql96-plpython.x86_64

Create new directory for 9.6
Continue reading “PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE”

12,639 total views, 2 views today

Tags: PostgreSQL, PostgreSQL Administration


Jun 15 2016

PostgreSQL Frequently Using Commands

Category: Administration,SQLFatih Acar @ 16:03

List, Add, Drop Database

List databases

postgres=# \d

Add database

postgres=# createdb testdb

or

postgres=# create database testdb with owner facar;


Drop database

postgres=# dropdb testdb

Postgres_logo

List, Add, Drop, Grant User and Schema

List Users

postgres=# select * from pg_user;

Add User

postgres=# create user facar with encrypted password ‘password’;

or

postgres=# createuser (interactive user create)

Change User Password

postgres=# \password facar
postgres=# alter user facar with encrypted password ‘newpassword’;

Create Schema

postgres=# create schema schfacar;
postgres=# grant usage on schema schfacar to facar;
postgres=# grant all on schema schfacar to facar;

Add Search Path

postgres=# alter user facar set search_path=schfacar;

Grant Permissions to User

postgres=# alter user facar superuser;
postgres=# alter user facar createdb;
postgres=# alter user facar replication;
postgres=# alter user facar bypassrls;
postgres=# grant all privileges on database testdb to facar;

Revoke Permissions from User

postgres=# alter user facar nosuperuser;
postgres=# alter user facar nocreatedb;
postgres=# alter user facar noreplication;
postgres=# alter user facar nobypassrls;
postgres=# revoke all privileges on database testdb from facar;

Connect Database

[root@testdb ~] su – postgres
-bash-4.2$ psql
-bash-4.2$ psql testdb (connect testdb)
-bash-4.2$ psql -U facar testdb (connect testdb with facar user)
-bash-4.2$ psql -U facar testdb -h “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 IP and 5432 port)

[root@testdb ~]  psql
[root@testdb ~]  psql testdb (connect testdb)
[root@testdb ~]  psql -U facar testdb (connect testdb with facar user)
[root@testdb ~]  psql -U facar testdb -h “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 IP and 5432 port)

Note : If you want to connect database with different user from postgres, you have to add line pg_hba.conf like that

host         all            all               192.168.20.12/32          md5

Information Queries

postgres=# \conninfo (current connection information)
postgres=# \dt (tables of current user schema)
postgres=# \d (list of object of current user schema)
postgres=# \d tablename (columns of tablename)
postgres=# \df (list of functions)
postgres=# \df+ (list of functions with details)
postgres=# select version(); (detailed version of database)
postgres=# \? (psql commands list for help)
postgres=# \h (list of help options)
postgres=# \h CREATE USER (help of CREATE USER command)
postgres=# \timing (open timing to show query execution time)
postgres=# \c testdb (connect testdb)
postgres=# \c testdb facar (connect testdb with facar user)
postgres=# select pg_database_size(current_database()); (size of current database as byte)
postgres=# select pg_database_size(‘testdb’); (size of testdb database as byte)
postgres=# select pg_relation_size(‘tablename’); (size of table as byte)
postgres=# select usename, datname from pg_stat_activity; (who are connected to which database now)
postgres=# select pg_reload_conf(); (to reload configurations like service postgresql reload)

4,579 total views, 2 views today

Tags: Database Administration, PostgreSQL, PostgreSQL Administration


Jun 14 2016

ERROR: database “database_name” is being accessed by other users in PostgreSQL

Category: Administration,Errors and SolutionsFatih Acar @ 08:57

Error

When you execute drop database command, database get an error like ERROR: database “database_name” is being accessed by other users.>

droppostgresqldatabase1

droppostgresqldatabase2

Solution
Continue reading “ERROR: database “database_name” is being accessed by other users in PostgreSQL”

2,692 total views, no views today

Tags: PostgreSQL, PostgreSQL Administration


Apr 10 2015

How to Delete psql Command Prompt History in PostgreSQL

Category: AdministrationFatih Acar @ 09:56

When you use psql command prompt, system automaticly saves your prompts. If you use password when user creates operation, this situation can occur security risk. Therefore, you can delete this prompts with editing psql_history file like below.

Find And Delete psql Prompts

[root@pg ~]# locate .psql_history
/var/lib/pgsql/.psql_history
[root@pg ~]# vi /var/lib/pgsql/.psql_history

39,689 total views, 10 views today

Tags: PostgreSQL, PostgreSQL Administration


Sep 21 2011

pg_dumpall with shell script and crontab in Linux

Category: Administration,Linux & UnixFatih Acar @ 13:18

You can take schedule backup with shell script. Firstly, You must create shell script file in operating system. After you can append to crontab.

Crontab is a UNIX command that creates a table or list of commands, each of which is to be executed by the operating system at a specified time. crontab is used to create the crontab file (the list) and later used to change the previously created crontab file.

Crontab

crontab -e Edit your crontab file, or create one if it doesn’t already exist.
crontab -l List your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file.

Minute Hour Day of Month Month Day of Week Command
(0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat)
0 2 12 * 0,6 /usr/bin/shellscriptname.sh

We will take backup wirh pg_dumpall. Firstly, You must create shell script.

Example

$>cd /etc/scripts/
$>vi shellscriptname.sh
Note : We created shellscriptname.sh with vi shellscriptname.sh statement. You have to write below script.

export today=`date +%Y%m%d`

export deleteday=`/bin/date –date=”15 days ago” +%Y%m%d`

##delete old backup script
rm -f /tmp/pgalldump_$deleteday.dump.out

##create backup script
pg_dumpall > /tmp/pgalldump_$today.dump.out

Note : After write, You close editor with ” escape > :wq! > enter “. You saved.

We wrote shell script. Now, We have to append to crontab.

Example

$>crontab -e
10 1 * * * /etc/scripts/shellscriptname.sh

Note : After write, You close editor with ” escape > :wq! > enter “. You saved. This time statement mean that shell script will execute every days of week,every month, every day, at 1 hour past 10 minute.

24,372 total views, no views today

Tags: Database Administration, Linux, PostgreSQL


Sep 09 2011

Data Dump in PostgreSQL

Category: AdministrationFatih Acar @ 09:11

You can use two statement for data dump. These statements are pg_dump and pg_dumpall. pg_dump can has table data, but pg_dumpall can has all database objects these are users, groups, stores and other object, so pg_dumpall has all database object. You can move database from product system to test system with pg_dumpall easyly.

Example

For take dump

postgres$ > pg_dump databasename > dumpname.dump.out Note:You can take backup only one database.
postgres$ > pg_dumpall > dumpname.dump.out Note:You can take all database object with this statement.

For restore from dump

postgres$ > psql -f dumpname.dump.out

9,416 total views, no views today

Tags: Database Administration, PostgreSQL, SQL