DEFINITIONS

Definitions More Info.
Definition ID899
TitlePOSTGRESQL
CategoryNOTES
DefinitionVACUUM and REINDEX WITH COPY psql commad
Definition Descriptionhttps://stackoverflow.com/questions/52986216/postgresql-dynamic-query-in-copy-stament-with-psql-command/52987247#52987247

LINUX İÇİN:

#!/bin/bash
#This backup process starts by postgres system account cron job.
#Bu backup islemi postgres sistem kullanicisinin cron job tarafindan calistirilmaktadir
#EditDate: 20190527 12:50
#Edited By: Cuneyd Tanriverdi
#[email protected]
#Asagidaki script database backuplarini almak icin yazilmistir

# Backup Start Date
Year=$(date +%Y)
Month=$(date +%m)
Day=$(date +%d)
current_time=$(date "+%Y%m%d%H%M%S")
backupPath=/storage/backup/$Year

psql -U postgres -d Turboard -c "COPY (SELECT format('REINDEX TABLE %s.%s', schemaname, relname) FROM pg_stat_all_tables ORDER BY n_dead_tup DESC) TO '/storage/pgscripts/Reindex_Turboard_$current_time.sql';"


WINDOWS ICIN:

-- eger sorguyu windows BAT icinde calistirmak istersek, asagidaki gibi komutu yazmak zorundayiz.

--ORIGINAL kod

psql -U postgres -c ^
"COPY ( ^
SELECT format('REINDEX TABLE %s.%s', schemaname, relname) ^
FROM pg_stat_all_tables ^
ORDER BY n_dead_tup DESC) ^
TO 'E:\scripts\maintenance\reindex.sql'"

-- Windows .BAT dosya icinde calistirmak istersen ve tirnak isareti koymak istersen iki tane %% isareti tirnak icinde "" yapmak zorundasin.

--REINDEX

psql -U postgres -c ^
"COPY ( ^
SELECT format('REINDEX TABLE ""%%s"".""%%s"";', schemaname, relname) ^
FROM pg_stat_all_tables ^
ORDER BY n_dead_tup DESC) ^
TO 'E:\scripts\maintenance\reindex.sql'"

--VACUUM
psql -U postgres -c ^
"COPY ( ^
SELECT format('VACUUM FULL VERBOSE ANALYZE ""%%s"".""%%s"";', schemaname, relname) ^
FROM pg_stat_all_tables ^
ORDER BY n_dead_tup DESC) ^
TO 'E:\scripts\maintenance\reindex.sql'"

-- asagidaki scripti bat olarak kaydedip windows komut satirindan calistirirsak bize COPY kismindaki postgresql deki sorguyu olusturacaktir.

setlocal ENABLEDELAYEDEXPANSION
echo off
cls
echo -- JIRAMAINTENNACE --

:: filename format Name-Date (eg MyDatabase-2009.5.19.bak)
::----------------------------------------------------------
@echo off
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "fullstamp=%YYYY%%MM%%DD%%HH%%Min%"

::%CD% bat dosyasinin bulundugu yerden calistirmaya yarar.
set SERVERNAME=JIRA-DB.gurmen.local
echo.

::REINDEX yapilacak indexler hazirlanir
::-------------------------------------
::copy nul E:\scripts\maintenance\PrapareReindexForBitbucket_%fullstamp%.sql

psql -U postgres -c ^
"COPY ( ^
SELECT format('REINDEX TABLE ""%%s"".""%%s"";', schemaname, relname) ^
FROM pg_stat_all_tables ^
ORDER BY n_dead_tup DESC) ^
TO 'E:\scripts\maintenance\reindex_%fullstamp%.sql'"
RecordBycunay
Record Date25-10-2018 16:53:59
Düzenle
Kopyala
Sil