Blog

Postgresql tablespace moving is two types they are: 1. Online moving – with database down 2. Offline moving – without database downwhy moving tablespace ? once default tablespace or non-default tablespace filesystem is full means you will get issue/error  at the …

postgresql tablespace moving is two types they are: 1. online moving – with database down 2. offline moving – without database down why moving tablespace ? once default tablespace or non-default tablespace filesystem is full means you will get issue/error  at …

Scenario: If Postgresql data partition is  running out of disk space How to solve this issue Ans: Option 1:Create a new tablespace on a separate drive and move existing tables to it. ALTER TABLE schema.table SET TABLESPACE tbs1; Or using …

Create a directory mkdir /home/Postdata/pg_data sudo chown postgres:postgres /home/Postdata/pg_data psql -d crsp -c “CREATE TABLESPACE tab1 OWNER u1 LOCATION ‘/home/Postdata/pg_data'”; If I wanted to move my database into this new tablespace: psql -d postgres -c “ALTER DATABASE db1 SET TABLESPACE …

how to find out the database and table a file path refers to on PostgreSQL database ? There are three main patterns for paths: 1.For files in the default tablespace: base/database_oid/filenode id for the relation 2.For files in Non-default tablespace: pg_tblspc / tablespace_oid …

Why Partition : Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance …

When you first create a new Ubuntu 16.04 server, there are a few configuration steps that you should take early on as part of the basic setup. This will increase the security and usability of your server and will give …

Step 1 – Installation of CFS dependencies CSF script requires perl modules , it is a csf dependencies module [root@nijam ~]# yum install perl-libwww-perl Loaded plugins: auto-update-debuginfo, fastestmirror, protectbase, refresh- : packagekit, security Setting up Install Process Loading mirror speeds …

Memory object caching systems like Memcached can optimize backend database performance by temporarily storing information in memory, retaining frequently or recently requested records. In this way, they reduce the number of direct requests to your databases. Because systems like Memcached …

Before Restoring & creating the database you have to restore the appropriate roles and have to give appropriate roles permission Step 1.bakup the roles by using following command Taking postgres Roles only backup : pg_dumpall -h localhost -p 5432 U …

this script will run at every night 1 AM and will take the postgresql basebackup of local tablespace # crontab -e 0 1 * * * sh /home/postgres/eds/pune.sh :wq #cat /home/postgres/eds/pune.sh # Config: DB=”POSTGRES_DB_NAME” U=”USERNAME” export PGPASSWORD=”tcs@12″ export PGPORT=”5444″ export …

You can’t take PostgreSQL functions backup like wise as tables in postgresql. You can use the below query to get all the function definitions from following script. # Config: DB=”LC” U=enterprisedb export PGPASSWORD=”tcs@345″ export PGPORT=”5544″ export PGHOST=”10.0.4.101″ nijam=”/home/Admin/” logfile=”nijam33″.sql /opt/edb/as9.6/bin/psql …

Here we written the script for taking postgresql physical backup with replication and witout replication Backup scripts for without replication: psql -U postgres -c “SELECT pg_start_backup(‘base backup’)” if [ “$?” != “0” ]; then echo Broken exit 1 fi tar …

In this Shell script will displays schema name and table name in which table having more than dead tubles in a postgresql server How to create & run shell script file ? create  a shell script file here created tables_dead_tubles.sh using vim …

If you are using PostgreSQL in a production environment, it is important to take precautions to ensure that your users’ data is not lost. By frequently backing up your database, and/or automating backups with a cron task, you will be …