Linux: Create a bootable usb key

It's pretty simple and straight forward to create bootable USB on linux.

dd if=image.iso of=/dev/sdX bs=4M && sync

with:

• if: Input FIle (your ISO)
• of: Output File. /dev/sdX represent your USB key. Replace X with the letter of your volume (can be found with fdisk -l)
• bs: Block Size
• sync: make sure all data in buffer are written to disk 

Linux: Change your lost root password

Careful, this doesn't work on Centos 7

On startup, go to rescue mode. e to edit the line.

Add int=/bin/bash in the linux/kernel line (depending if you're using grub or grub2). Start it.
When on the command line, mount your file system and change your password:

# mount -o rw, remount /
# passwd
# touch / .autorelabel
# /sbin/reboot -f

Now, you have a new root password ;)

GRUB

Configuration

The default loader for many Linux distributions, including enterprise Red Hat 5 and 6 , is GRUB (Grand Unified Bootloader). It is highly configurable, in particular by accepting encrypted password protection, using shell or graphics mode. It is configured with a text file and there is no need to reinstall GRUB with each change. Although replaced by GRUB2 in all distributions, do not think that GRUB has disappeared. There are strong chances, if you use Amazon EC2 instances on Linux, that the used boot loader is its first version, called "legacy".

Here is an example of a configuration file assuming that the first partition of the first disk is /boot, and that the second contains a Windows installation:

timeout=10
default=0
title Red Hat
    root (hd0,0)
    kernel /vmlinuz-2.6.12-15 ro root=LABEL=/
    initrd /initrd-2.6.12-15.img
title Windows XP
    rootnoverify (hd0,1)
    chainloader +1

Syntax: 

GRUB Parameter

Description


chainloader +1
Start the first sector or the root specified above
default n
Default boot (0=first title, 1=second title, etc.)
gfx menu
Path to graphic menu
initrd
Initial ramdisk. The kernel will load this file in memory to find configuration details and initial drivers.
kernel
Name of the kernel image, folowed by its parameters. The / doesn't represent the file system root, but the one of (hd0,0), so /boot/vmlinuz...
root(hdx, y)
All the file accesses will be from this partition (explained below). Here, hd0,0 represent the first partition of the first disk detected by the BIOS. It's the /boot partition
rootnoverify
the specified root, not mounted by GRUB (it doesn't support NTFS)
timeout
Timeout before default boot
title xxxx
Start of a section, entry in GRUB menu

Installation

GRUB configuration can be found in /etc/grub.conf or /boot/grub/menu.lst (symbolic link). GRUB can be install on the MBR (Master Boot Record, 512 first bits of a disk) or PBR (Partition Boot Record, 512 first bits of a partistion).

To install or re-install GRUB - in case of a corrupted MBR, for example on /dev/sda -, use:
#/sbin/grub-install /dev/sda


Start and Edit

Options on the GRUB menu

e: edit the line ( adding parameters to the kernel or init)
d: delete a line
o: add a line
b: start the image


If you press Esc there are a grub interpreter available.

Postgres SQL: pg_dump / pg_restore

Backup a specific table:

pg_dump --table TABLENAME -U postgres dbName -f /home/postgres/TABLENAME.sql

If there is a table with the same name in another schema, use the --schema SCHEMANAME option

Postgres SQL: User management

alter user POSTGRES with password 'newPassword';

Postgres SQL: Database admin

DB size

select pg_size_pretty(pg_database_size('DB1'));

pg_size_pretty
----------------
 48 GB

Non default parameters

select name, setting, boot_val from pg_settings where setting != boot_val;
              name              |            setting             |     boot_val  
--------------------------------+--------------------------------+-------------------
 archive_command                | cp %p /backup/DB1/current/%f |
 archive_mode                   | on                             | off
 archive_timeout                | 600                            | 0
 bgwriter_lru_maxpages          | 200                            | 100
 checkpoint_segments            | 100                            | 3
 client_encoding                | UTF8                           | SQL_ASCII
 deadlock_timeout               | 10000                          | 1000
 default_text_search_config     | pg_catalog.english             | pg_catalog.simple
 effective_cache_size           | 13107200                       | 16384
 lc_collate                     | en_US.UTF-8                    | C
 lc_ctype                       | en_US.UTF-8                    | C
 lc_messages                    | en_US.UTF-8                    |
 lc_monetary                    | en_US.UTF-8                    | C
 lc_numeric                     | en_US.UTF-8                    | C
 lc_time                        | en_US.UTF-8                    | C
 listen_addresses               | *                              | localhost
 log_autovacuum_min_duration    | 0                              | -1
 log_checkpoints                | on                             | off
 log_connections                | on                             | off
 log_directory                  | /podom_02/pg_log               | pg_log
 log_duration                   | on                             | off
 log_hostname                   | on                             | off
 log_line_prefix                | %d %u %r %p %c                 |
 log_lock_waits                 | on                             | off
 log_min_duration_statement     | 1000                           | -1
 log_min_messages               | notice                         | warning
 log_rotation_size              | 0                              | 10240
 log_statement                  | ddl                            | none
 log_statement_stats            | on                             | off
 log_temp_files                 | 0                              | -1
 log_timezone                   | Europe/Paris                   | UNKNOWN
 log_truncate_on_rotation       | on                             | off
 logging_collector              | on                             | off
 maintenance_work_mem           | 1048576                        | 16384
 max_connections                | 200                            | 100
 max_stack_depth                | 2048                           | 100
 random_page_cost               | 1                              | 4
 server_encoding                | UTF8                           | SQL_ASCII
 shared_buffers                 | 1048576                        | 1024
 superuser_reserved_connections | 5                              | 3
 TimeZone                       | Europe/Paris                   | UNKNOWN
 timezone_abbreviations         | Default                        | UNKNOWN
 wal_buffers                    | 2048                           | 8
 work_mem                       | 32768                          | 1024
(44 rows)

This shows all of the server configuration changes made via updates to the postgresql.conf file, from a running server:

SELECT version();
SELECT name, current_setting(name), SOURCE
  FROM pg_settings
  WHERE SOURCE NOT IN ('default', 'override');

Postgres SQL: Functions

You can find basic information regarding a function with the command \df

zabbixdb=> \df function_name                                 List of functions
 Schema |      Name     | Result data type | Argument data types |  Type
--------+------------------------+------------------+-----------------------
 public | function_name | trigger          |                     | trigger

You can see the source code with this query:

testdb=> select prosrc from pg_proc where proname='function_or_procedure_name';<br />

Execute a VOID function:

testdb=> select functionName();

Execute a VOID function with named parameters:

testdb=> select functionName(param1:='valueText', param2:=valueInt);

If you need more information, here is the pg_proc table:

testdb=> \d+ pg_proc
                             Table "pg_catalog.pg_proc"
     Column      |     Type     | Modifiers | Storage  | Stats target | Description
-----------------+--------------+-----------+----------+--------------+-------------
 proname         | name         | not null  | plain    |              |
 pronamespace    | oid          | not null  | plain    |              |
 proowner        | oid          | not null  | plain    |              |
 prolang         | oid          | not null  | plain    |              |
 procost         | real         | not null  | plain    |              |
 prorows         | real         | not null  | plain    |              |
 provariadic     | oid          | not null  | plain    |              |
 protransform    | regproc      | not null  | plain    |              |
 proisagg        | boolean      | not null  | plain    |              |
 proiswindow     | boolean      | not null  | plain    |              |
 prosecdef       | boolean      | not null  | plain    |              |
 proleakproof    | boolean      | not null  | plain    |              |
 proisstrict     | boolean      | not null  | plain    |              |
 proretset       | boolean      | not null  | plain    |              |
 provolatile     | "char"       | not null  | plain    |              |
 pronargs        | smallint     | not null  | plain    |              |
 pronargdefaults | smallint     | not null  | plain    |              |
 prorettype      | oid          | not null  | plain    |              |
 proargtypes     | oidvector    | not null  | plain    |              |
 proallargtypes  | oid[]        |           | extended |              |
 proargmodes     | "char"[]     |           | extended |              |
 proargnames     | text[]       |           | extended |              |
 proargdefaults  | pg_node_tree |           | extended |              |
 prosrc          | text         |           | extended |              |
 probin          | text         |           | extended |              |
 proconfig       | text[]       |           | extended |              |
 proacl          | aclitem[]    |           | extended |              |
Indexes:
    "pg_proc_oid_index" UNIQUE, btree (oid)
    "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, proargtypes, pronamespace)
Has OIDs: yes

Postgres SQL: Tables

Table size

with index:

select pg_size_pretty(pg_total_relation_size('TABLENAME'));
 pg_size_pretty
----------------
 26 GB

without index:

select pg_size_pretty(pg_relation_size('TABLENAME'));
 pg_size_pretty
----------------
 11 GB

Tables number of lines:

select schemaname, relname, n_live_tup from pg_stat_all_tables where n_live_tup > 0 order by n_live_tup desc;

Postgres SQL: Indexes

invalid indexes:

select count(*) from pg_index where indisvalid = false

number of indexes per tables:

select tablename, count(*) from pg_indexes where schemaname != 'pg_catalog' group by tablename having count(*) > 4 order by 2 desc;
   tablename   | count
---------------+-------
 table1      |    18
 table2         |    18
 table3 |    15
 table4        |    13

Index usage

SELECT
    i.idx_scan,
    i.idx_tup_read,
    i.idx_tup_fetch,
    i.indexrelname AS index,
    it.spcname AS index_tablespace,
    i.relname AS table,
    tt.spcname AS table_tablespace,
    pg_size_pretty(pg_relation_size(i.indexrelname::text)) as index_size
FROM pg_stat_all_indexes i
    INNER JOIN pg_class ic ON (i.indexrelid = ic.oid)
    LEFT OUTER JOIN pg_tablespace it ON (ic.reltablespace = it.oid)
    INNER JOIN pg_class tc ON (i.relid = tc.oid)
    LEFT OUTER JOIN pg_tablespace tt ON (tc.reltablespace = tt.oid)
ORDER BY 1 desc, 2 desc, 3 desc

Index not used

select * from pg_stat_all_indexes
where schemaname <> 'pg_catalog'
and schemaname <> 'pg_toast'
and idx_scan = 0
and idx_tup_read = 0
and idx_tup_fetch = 0
and indexrelid not in (select indexrelid from pg_index where indisunique = true or indisprimary = true
);

Postgres SQL: Sessions

Global overview:

v8.3

SELECT
    sum(CASE WHEN waiting THEN 1 ELSE 0 END) AS waiting,
    sum(CASE WHEN current_query='' THEN 1 ELSE 0 END) AS idle,
    sum(CASE WHEN current_query=' in transaction' THEN 1 ELSE 0 END) AS idletransaction,
    sum(CASE WHEN current_query='' THEN 1 ELSE 0 END) as unknown,
    sum(CASE WHEN NOT waiting AND current_query NOT IN ('', ' in transaction', '') THEN 1 ELSE 0 END) AS active
FROM pg_stat_activity WHERE procpid != pg_backend_pid() and datname = current_database();

v9.3

all sessions (full)

select * from pg_stat_activity

all sessions (lite)

select datname, usename, state, waiting, query from pg_stat_activity;

Sessions state per DB

select datname, state, count(*) from pg_stat_activity group by datname, state order by 1, 2;

nb sessions waiting

select waiting, count(*) from pg_stat_activity group by waiting;

nb sessions waiting per DB

select datname, waiting, count(*) from pg_stat_activity group by datname, waiting order by 1, 2;

querries per DB

select datname, query, count(*) from pg_stat_activity group by datname, query order by 1,3 desc