This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Get the current timestamp in ISO 8601 format and adjust it to the desired format | |
var timestamp = new Date().toISOString().replace(/T/, ' ').replace(/\..+/, ''); | |
// Print the header line for the CSV, including database name and timestamp | |
print("databaseName,timestamp,collectionName,documentCount,collectionSizeMB"); | |
// Get the name of the current database | |
var dbName = db.getName(); | |
// Get the list of all collection names in the current database |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT n.nspname AS schema_name | |
,c.relname AS table_or_index_name | |
,c.relkind AS table_or_index | |
,c.reltuples AS row_count | |
,s.last_vacuum | |
,s.last_autovacuum | |
,s.last_analyze | |
,s.last_autoanalyze | |
FROM pg_class c | |
JOIN pg_namespace n ON n.oid = c.relnamespace |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Display vacuum information for PostgreSQL Tables | |
SELECT n.nspname AS schema_name | |
, c.relname AS table_name | |
, c.reltuples AS row_count | |
, c.relpages AS page_count | |
, s.n_dead_tup AS dead_row_count | |
, s.last_vacuum | |
, s.last_autovacuum | |
, s.last_analyze | |
, s.last_autoanalyze |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- The following script emulates the PostgreSQL commmand | |
-- \du+ using SQL instead of the command | |
SELECT r.rolname | |
,r.rolsuper | |
,r.rolinherit | |
,r.rolcreaterole | |
,r.rolcreatedb | |
,r.rolcanlogin | |
,r.rolreplication | |
,r.rolconnlimit |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with results as ( | |
SELECT rug.grantor, rug.grantee, rug.object_catalog, rug.object_schema, rug.object_name, rug.object_type, rug.privilege_type, rug.is_grantable, null::text AS with_hierarchy | |
FROM information_schema.role_usage_grants rug | |
WHERE rug.object_schema NOT IN ('pg_catalog', 'information_schema') | |
AND grantor <> grantee | |
UNION | |
SELECT rtg.grantor, rtg.grantee, rtg.table_catalog, rtg.table_schema, rtg.table_name, tab.table_type, rtg.privilege_type, rtg.is_grantable, rtg.with_hierarchy | |
FROM information_schema.role_table_grants rtg | |
LEFT JOIN information_schema.tables tab | |
ON (tab.table_catalog = rtg.table_catalog AND tab.table_schema = rtg.table_schema AND tab.table_name = rtg.table_name) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# /etc/logrotate.d/pgbouncer | |
/var/log/postgresql/pgbouncer.log { | |
daily | |
missingok | |
rotate 7 | |
compress | |
delaycompress | |
notifempty | |
create 640 postgres adm | |
sharedscripts |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Delete permissions and drop a user (ROLE) from a | |
-- postgres database. | |
-- Be sure to update the following: | |
-- <username_to_delete> - The target user name | |
-- <your_database_name> - The target database name | |
-- <schema_name> - The target schema. | |
DO $$ | |
DECLARE | |
r RECORD; | |
BEGIN |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- A Quick query to view postgresql grant information | |
-- Work in progress | |
SELECT | |
'TABLE' AS object_type, | |
grantee, | |
table_schema, | |
table_name, | |
privilege_type | |
FROM | |
information_schema.role_table_grants |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Import SQL Server module | |
Import-Module "SQLPS" -DisableNameChecking | |
# Variables | |
$ServerName = "YourServerName" # Enter your server name here | |
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName | |
# Get the availability groups | |
$AvailabilityGroups = $Server.AvailabilityGroups |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- The following command will generate the | |
-- Statistics Rebuild command for all of the | |
-- statistics on a the targeted schema and table | |
select 'update statistics ' + sc.name + '.' + so.name + '(' + stat.name + ') with fullscan;' as Script | |
, stats_date(stat.object_id, stat.stats_id) as last_updated | |
, stuff(( | |
select ', ' + cols.name | |
from sys.stats_columns as statcols | |
join sys.columns as cols | |
on statcols.column_id = cols.column_id |
NewerOlder