postgres-terminate-connection

2014-05-13 18:23

Greg Stencel

select pg_terminate_backend(pid)
from pg_stat_activity;

watch

2014-05-13 17:58

Greg Stencel

Comments

Watch specified processes every second watch -n 1 'ps aux | grep ssh'

running-scripts-in-background-linux

2014-05-13 14:01

Greg Stencel

Comments

I used to execute a lot of commands with & at the end. But once I used plowshare and it didnt work because & works until you wont log out .So my solution (I know I could use nohup :) ) was screen. Screen is the best tool admin can use when working remotely. It can be usefull when you're in place with big packet loss and you often lost connection so when you're restoring database you should be in screen session because when something will disconnect you you can alwas log in and connect to screen session in which restoring is taking place.

So executing

$./my_script.sh &

will be only running when your session is still active so when you'll logout it will be killed.

To run it independent from your presence on server do it with nohup:

$ nohup ./my_script.sh &

To launch screen :

$ screen

Best practice is to launch screen with its name :

$ screen -S my_own_screen_name

Then if you want detach screen to get back later Ctrlad If you want terminate screen just Ctrld or exit

then you can resume it with:

$ screen -r my_own_screen_name

To list all available screens :

$ screen -ls

To attach to already attached screen:

$ screen -x my_own_screen_name

To attach to attached screen and detach others:

$ screen -r -d my_own_screen_name

To rename existing screen:

(being attached): Ctrla:sessionname my_screen_nameEnter

Easy admin joke

2014-05-10 12:31

Greg Stencel

Comments

When I first got admin access ages ago on shared server I wanted to do something funny to my coleague so I thought to display something on his screen.

Remembering everything in Linux filesystem is a file or a directory I read about pseudoterminals "pts" they are inside /dev directory

Yea yea I know 3 rules of admin : Respect the privacy of others. Think before you type. With great power comes great responsibility bla bla bla ...

To do this joke just check who is logged in and on which pts is he :

$ who
greg     pts/3        2012-03-10 1:26 (:0)
mark     pts/5        2012-03-10 10:29 (:0)
jenny    pts/7        2012-03-10 13:06 (:0)

So now, we know that Mark is working on pts/5 so lets send him message :)

$ echo "In 5 minutes in toilet /Jenny" > /dev/pts/5

To "clean evidences" :D ( remember that guy working on this terminal will lost his current works he's working on this specific terminal)

$ cat /dev/urandom > /dev/pts/5

MySQL

2014-05-08 12:49

Greg Stencel

Comments

MySQL tips:

Create database with charset utf8

CREATE DATABASE mynew_db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

Grant everything to local user on all tables

GRANT ALL PRIVILEGES ON db_name.* TO my_user@localhost IDENTIFIED BY 'my_secret_password';

Grant everything to remote user on all tables

GRANT ALL PRIVILEGES ON  db_name.* TO my_user@'IP_of_host_user_connects_from' IDENTIFIED BY 'my_secret_password';

If you want to add only SELECT or something else instead of ALL there are other available privileges like:

SELECT, UPDATE, ALTER, CREATE, DELETE, DROP, SHOW DATABASES CREATE TEMPORARY TABLES, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, SUPER, USAGE

After all you want to reload privileges because when mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point. To tell the server to reload the grant tables, perform a flush-privileges operation.

Execute sql :

FLUSH PRIVILEGES ;

or you can even do it from shell by

$ mysqladmin flush-privileges

or

$ mysqladmin reload

postgres

2014-05-08 10:20

Greg Stencel

Comments

Quick tip:

I started with MySQL db (not speaking of Delphi ADO or something like this ) Now because of my job I had to switch to Postgres and I thought "ok but it's SQL so it will be the same" - not really I had a lot of problems like in the beginning like user privileges owners of databases and tables, showing tables , showing databases So if youre switchng from mysql it can help you for beginning:

So first problem was my user access - I couldnt access postgres even through root :) so after clean installation if you want to launch psql client for postgres just su as postgres after that you can create your own super user or user having privileges to specific database

$ su postgres
$ psql

and then you should be able to do everything

To see databases "show databases;" didn't work anymore :) in postgres

\list
\l

connect to database:

\c db_name

show tables; in database:

\dt

Explain or describe table:

\d+ table_name

Show all shortcuts:

\?

Backup

To dump database to sql script with inserts etc use pg_dump remember (it wont dump user privileges):

pg_dump my_database > my_database.pgdump

The better way of dumping database is pg_dumpall it dumps database users and groups, tablespaces, access permissions that apply to databases as a whole.

pg_dumpall > my_dump.pgdump

If you want to move your users of postgres to different server you can:

pg_dumpall --roles-only > my_users.pgdump

Restore

TO simply restore backup pgdump sql file :

psql < my_database.pgdump

Access Privileges:

Another way is about accessing postgresql if you worked with mysql you could just provide : grant all privileges on db to user_name@localhost or user_name@'10.0.0.1' here it's not the same. Privileges can be configured in low level pg_hba.conf file where you provide who(sys user) who(db_user) where_from . Adding user in postgres:

Create user:

create user my_name with password 'secret';

change owner of db:

alter database mydatabase  owner to new_owner_name;

change owner of tables:

REASSIGN OWNED BY old_role TO new_role

rename db

alter  database production rename to production_delete;

Drone - Aerial photos

2014-05-03 16:24

Greg Stencel

Comments