Postgres |
|
I am working on an application thats backend is Postgres and SQL Server. Here are some of the things I have learned working with Postgres.
Concatenate a string |
|
Use the double pipe "|" to concatenate a string.
SELECT 'drop table ' || table_name||';'
FROM information_schema.tables
where table_name like 'sym%'
SELECT 'drop table ' || table_name||';'
FROM information_schema.tables
where table_name like 'sym%'
Create Drop Statement for multiple tables |
|
Needed to drop a bunch of tables . Here you go
SELECT 'drop table ' || table_name||';'
FROM information_schema.tables
where table_name like 'sym%'
SELECT 'drop table ' || table_name||';'
FROM information_schema.tables
where table_name like 'sym%'
Copy Out Tables in CSV format to a directory |
|
Had a request to copy a few tables with data to a directory so they could import them into a different database on a different server. Here is what I came up with. This generates the SQL statements to be run.
--copy table to the file path
SELECT 'copy ' || table_name||' to '||'''/home/postgres/'||table_name||'.csv'||''''||' Delimiter '||''','''||' CSV;'
FROM information_schema.tables
where table_name like 'sym%'
--this is the destination that the CSV will be imported into
SELECT 'copy ' || table_name||' from '||'''/home/postgres/'||table_name||'.csv'||''''||' Delimiter '||''','''||' CSV;'
FROM information_schema.tables
where table_name like 'sym%'
--Copy a specific query to a file
COPY (select * from blah_table where name = 'yo momma') TO '/home/postgres/blah_table.csv' DELIMITER ',' CSV HEADER;
Here is the Postgres documentation
http://www.postgresql.org/docs/8.4/interactive/sql-copy.html
--copy table to the file path
SELECT 'copy ' || table_name||' to '||'''/home/postgres/'||table_name||'.csv'||''''||' Delimiter '||''','''||' CSV;'
FROM information_schema.tables
where table_name like 'sym%'
--this is the destination that the CSV will be imported into
SELECT 'copy ' || table_name||' from '||'''/home/postgres/'||table_name||'.csv'||''''||' Delimiter '||''','''||' CSV;'
FROM information_schema.tables
where table_name like 'sym%'
--Copy a specific query to a file
COPY (select * from blah_table where name = 'yo momma') TO '/home/postgres/blah_table.csv' DELIMITER ',' CSV HEADER;
Here is the Postgres documentation
http://www.postgresql.org/docs/8.4/interactive/sql-copy.html
MVCC |
|
How does Postgres handle concurrency? Its a lot like Oracle and optimistic locking or Read Committed Snapshot Isolation for SQL SQL Server.
Mutli-Version Concurrency Control
Mutli-Version Concurrency Control
Remove double quotes from copied output from the results grid to query window in PGAdminIII |
|
When copying output from a query in PGAdmin III the results include double quotes around the results and its a real pain in the butt. In the window with the object browser, not sure of the name follow the below steps...
Select File->Options
Click on Query Tool->Results Grid and set Result copy quoting to NONE
Select File->Options
Click on Query Tool->Results Grid and set Result copy quoting to NONE
Find Foreign Key Dependencies |
|
Need to find all your FK dependencies. Here you go
select c.constraint_name
, x.table_name as primary_table
, x.column_name as source_Key_column
, y.table_name as foreign_key_table_name
, y.column_name as foreign_key_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
on y.ordinal_position = x.position_in_unique_constraint
and y.constraint_name = c.unique_constraint_name
order by c.constraint_name, x.ordinal_position
select c.constraint_name
, x.table_name as primary_table
, x.column_name as source_Key_column
, y.table_name as foreign_key_table_name
, y.column_name as foreign_key_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
on y.ordinal_position = x.position_in_unique_constraint
and y.constraint_name = c.unique_constraint_name
order by c.constraint_name, x.ordinal_position
Find the size of your tables |
|
Here you go.
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 50;
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 50;
Find Tables that need to be Vacuumed |
|
Find tables that need to be vacuumed based on number of dead rows vs live rows.
select 'vacuum full '||relname,
relname as TableName,
n_live_tup as LiveRows,
n_dead_tup as DeadRows,
last_vacuum,
last_autovacuum,
last_autoanalyze,
round((n_dead_tup*1.0)/(n_live_tup*1.0),4) as percentage_decimal,
round(((n_dead_tup*1.0)/(n_live_tup*1.0)*100),2) as percentage_of_dead_VS_Live_Rows
from pg_stat_user_tables
where n_live_tup <>0 and n_dead_tup <>0 and n_dead_tup > 100
order by percentage_of_dead_VS_Live_Rows desc
select 'vacuum full '||relname,
relname as TableName,
n_live_tup as LiveRows,
n_dead_tup as DeadRows,
last_vacuum,
last_autovacuum,
last_autoanalyze,
round((n_dead_tup*1.0)/(n_live_tup*1.0),4) as percentage_decimal,
round(((n_dead_tup*1.0)/(n_live_tup*1.0)*100),2) as percentage_of_dead_VS_Live_Rows
from pg_stat_user_tables
where n_live_tup <>0 and n_dead_tup <>0 and n_dead_tup > 100
order by percentage_of_dead_VS_Live_Rows desc
Cast in Postgres |
|
Cast an object with the double colons and then the specified data type
select <some field>::text
select <some field>::text
MD5 Function |
|
"The MD5 message-digest algorithm is a widely used cryptographic hash function producing a 128-bit (16-byte) hash value, typically expressed in text format as a 32 digit hexadecimal number."
from Wikipedia
Here is how to create that hashed random number cast as a text data type.
select MD5(random()::text)
from Wikipedia
Here is how to create that hashed random number cast as a text data type.
select MD5(random()::text)
Find row count for all tables in Postgres
Need a row count for all your tables. Writing a cursor in Postgres is a beat down. I found this.
SELECT
nspname AS schemaname,
relname as TableName,
reltuples as RowCount
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND relkind = 'r'
ORDER BY
reltuples DESC;
SELECT
nspname AS schemaname,
relname as TableName,
reltuples as RowCount
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND relkind = 'r'
ORDER BY
reltuples DESC;
Split a string |
|
This is a made up URL endpoint
https://x22v09-aaa.2020.surfingdba.com:888
using the split_part function this is really easy. Actually easier than in SQL Server.
select split_part(split_part('https://x22v09-aaa.2020.surfingdba.com:888','//',2),'-',1)
1 returns the left of the first split
2 returns the right of the first split
3 returns the right of the second split
etc...
https://x22v09-aaa.2020.surfingdba.com:888
using the split_part function this is really easy. Actually easier than in SQL Server.
select split_part(split_part('https://x22v09-aaa.2020.surfingdba.com:888','//',2),'-',1)
1 returns the left of the first split
2 returns the right of the first split
3 returns the right of the second split
etc...
Create a temp table |
|
Basically just like SQl Server
SELECT *
into TEMPORARY yo_Momma
FROM some_table
Drop table yo_momma
SELECT *
into TEMPORARY yo_Momma
FROM some_table
Drop table yo_momma
Dateadd Function in Postgres |
|
There isn't a straight up DateAdd function in Postgres like in SQL Server. here is the equivalent
select now()-(15 * interval'1 minute')
select now()-(15 * interval'1 minute')
How long has Postgres been up??
Find how long postgres has been up
select pg_postmaster_start_time()
select pg_postmaster_start_time()
How long a transaction has been open? |
|
using pg_stat_activity to find longest running tran
SELECT max(now() - xact_query) FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
select datname, pid, client_addr, client_port, backend_start, xact_start, query_start, state_change, waiting, state, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')-- and backend_start < '2016-09-01'
order by xact_start;
SELECT max(now() - xact_query) FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
select datname, pid, client_addr, client_port, backend_start, xact_start, query_start, state_change, waiting, state, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')-- and backend_start < '2016-09-01'
order by xact_start;
How are your tables being accessed? |
|
Is the data being read by table scans or via indexes
select relname as "table",
seq_scan as "table scans",
seq_tup_read as "tuples scanned",
idx_scan as "index lookups",
idx_tup_fetch as "tuples fetched via index"
from pg_stat_user_tables
select relname as "table",
seq_scan as "table scans",
seq_tup_read as "tuples scanned",
idx_scan as "index lookups",
idx_tup_fetch as "tuples fetched via index"
from pg_stat_user_tables
Return data only from PSQL command line!!! |
|
When you run a query from the interactive PSQL terminal you can get some unwanted stuff. It's a real pain in the butt if you're writing scripts.
You get column headers and a line break and a footer telling how many rows returned.
You can turn off this info returned with your queries but using the -t flag.
psql -d <your database name> -t -c "SELECT pid, query_start, state, waiting, query FROM pg_stat_activity
where state in ('idle', 'idle in transaction') and waiting = 'f';"
You get column headers and a line break and a footer telling how many rows returned.
You can turn off this info returned with your queries but using the -t flag.
psql -d <your database name> -t -c "SELECT pid, query_start, state, waiting, query FROM pg_stat_activity
where state in ('idle', 'idle in transaction') and waiting = 'f';"
PG Kill process |
|
If you need to kill process in Postgres this will do it.
select datname, pid, xact_start, query_start, state, query from pg_stat_activity
--kills the connection
select pg_terminate_backend(<PID>)
--kills query but leaves connection intact
select pg_cancel_backend(<PID>)
--can easily kill multiple processes as seen below and not kill your own session
select pg_cancel_backend(pid) from pg_stat_activity
where state in('idle in transaction','idle') and pid <> (select pg_backend_pid())
select pg_terminate_backend(pid) from pg_stat_activity
where state in('idle in transaction','idle') and pid <> (select pg_backend_pid())
select datname, pid, xact_start, query_start, state, query from pg_stat_activity
--kills the connection
select pg_terminate_backend(<PID>)
--kills query but leaves connection intact
select pg_cancel_backend(<PID>)
--can easily kill multiple processes as seen below and not kill your own session
select pg_cancel_backend(pid) from pg_stat_activity
where state in('idle in transaction','idle') and pid <> (select pg_backend_pid())
select pg_terminate_backend(pid) from pg_stat_activity
where state in('idle in transaction','idle') and pid <> (select pg_backend_pid())
Handle Nulls in your Query |
|
This query below returns 9999-01-01 for null values and also replaces line breaks with replace(query,E'\n','')
SELECT pid, COALESCE(split_part(query_start::text,'+',1),'9999-01-01'::text) as query_start, state, waiting, replace(query,E'\n','') as query FROM pg_stat_activity
where state in ('idle', 'idle in transaction') and datname ='<dbname >' and waiting = 'f';
SELECT pid, COALESCE(split_part(query_start::text,'+',1),'9999-01-01'::text) as query_start, state, waiting, replace(query,E'\n','') as query FROM pg_stat_activity
where state in ('idle', 'idle in transaction') and datname ='<dbname >' and waiting = 'f';
Window Functions in Postgres |
|
Just like SQL Server.... Hell yes!!!!
select *,count(cp_node) over(partition by vrealm) as cp_count
into temporary final
from cp_check;
select * from final
where cp_count < 4;
select *,count(cp_node) over(partition by vrealm) as cp_count
into temporary final
from cp_check;
select * from final
where cp_count < 4;
Find Postgres Error Logs |
|
Need to find the PG error logs???
Go to the psql shell as postgres user.
postgres=# show logging_collector;
this will show if you are collecting logs for Postgres. I have not researched how to turn it on yet. It just is in my environment.
postgres=# show log_directory;
the above will show the log directory. In my example the directory is found at...
/var/log/postgresql
Go to the psql shell as postgres user.
postgres=# show logging_collector;
this will show if you are collecting logs for Postgres. I have not researched how to turn it on yet. It just is in my environment.
postgres=# show log_directory;
the above will show the log directory. In my example the directory is found at...
/var/log/postgresql
Query Postgres config settings |
|
Find you postgres config vaules.
Query the postgres.conf file.
log into psql shell...
select name, setting, short_desc, boot_val from pg_settings;
Query the postgres.conf file.
log into psql shell...
select name, setting, short_desc, boot_val from pg_settings;
Find postgres data directory |
|
Can't find the postgres data directory...
log into postgres
show data_directory;
log into postgres
show data_directory;
Find Replication Lag to your slave |
|
I need to find the lag time from Master to slave regarding replication...
Run this on the slave.
select now() - pg_last_xact_replay_timestamp() AS replication_delay;
Run this on the slave.
select now() - pg_last_xact_replay_timestamp() AS replication_delay;
Change the Owner of a Table |
|
I need to change the owner of the table Yo_Momma to the user Yo_Momma
Execute in the context of the database as a superuser.
Alter Table Yo_Momma owner to 'Yo_Momma';
Execute in the context of the database as a superuser.
Alter Table Yo_Momma owner to 'Yo_Momma';
Find what permission a user has on a table |
|
I was having permissions issues with customer and their specific table they had created. To troubleshoot I used this query..
SELECT grantor, grantee, privilege_type,table_name
FROM information_schema.role_table_grants where table_name = '<table name>';
SELECT grantor, grantee, privilege_type,table_name
FROM information_schema.role_table_grants where table_name = '<table name>';
PG_CTL and Debian |
|
Not sure why but PG_CTL is weird on debian.
do it like this
/usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main restart -o "--config_file=/etc/postgresql/9.4/main/postgresql.conf"
do it like this
/usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main restart -o "--config_file=/etc/postgresql/9.4/main/postgresql.conf"