Postgresで全テーブルの件数を表示する

backuprestore.cmd

set BACKUPDAY=20180211
set DB_NAME=testdb
set EXE_PATH=C:\opt\PostgreSQLPortable_9.6.1\App\PgSQL\bin
set BACKUP_FILE_PATH=C:\opt
set LOG_NAME=%BACKUP_FILE_PATH%\log\%DB_NAME%_log_%BACKUPDAY%.log
set BACKUP_NAME=%BACKUP_FILE_PATH%\data\%DB_NAME%_db_%BACKUPDAY%.sql

REM ポータブルDBからバックアップ（サーバ起動状態）

echo 開始 %DATE% %TIME% > %LOG_NAME%
"%POST_PATH%\pg_dump.exe" -h localhost -p 5432 -U postgres --verbose --file "%BACKUP_NAME%" "%DB_NAME%" 2>> %LOG_NAME%

REM ポータブルDBにリストア（サーバ起動状態）

"%EXE_PATH%\dropdb.exe" -U postgres %DB_NAME%
"%EXE_PATH%\createdb.exe" -U postgres %DB_NAME%
"%EXE_PATH%\psql.exe" -U postgres -d %DB_NAME% < "%BACKUP_NAME%"
echo 終了 %DATE% %TIME% >> %LOG_NAME%

postgresql.md

postgresq table 件数

https://gist.github.com/smd877/3877864

select relname
     , n_live_tup 
from pg_stat_user_tables w
here schemaname='public';

SELECT T2.relname
     , T2.reltuples 
FROM pg_stat_user_tables AS T1 
INNER JOIN pg_class AS T2 
   ON T1.relname = T2.relname 
ORDER BY T2.relname;

SELECT relname, n_tup_ins - n_tup_del as rowcount 
FROM pg_stat_all_tables

select relname, n_live_tup 
from pg_stat_user_tables 
where schemaname='public';