PostgreSQL cheat sheet

Reference: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

sudo -i -u postgres

psql


1. To create a user

#create user unais with password '123';

==> To make superuser

#alter user unais with superuser;
select * from sessions limit 10;
2. To create database

#create database dbname;

3. To grant all privileges on a db to a user

#grant all privileges on database dbname to dbuser;

4. To log in to a single database

psql -U unais dbname;

If peer authentication fails, edit /etc/postgresql/9.1/main/pg_hba.conf

local              all                                     postgres                     all

TO

local             all                                      postgres                     md5

Peer authentication

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

And then reload PostgreSQL

5. Error while loading application over browser

psql: FATAL: Peer authentication failed for user

Change in pg_hba.conf

local  all  all  peer    TO    local  all  all  trust

6. To list all the databases

# \list

6. To list all the tables

#\dt

6. To list roles

\du

6. To give permission to a user/role on a database where another user has full permission

GRANT user1 to user2;

then check using du -

                              List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+------------
 db1     | Superuser, Create role, Create DB, Replication | {}
 user1  | Create DB                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 user2                                                                      {user1}                                                                            

6. To list limited entry or rows

#select * from table_name limit 10;

7. To truncate a table

TRUNCATE TABLE  table_name;

8. To list size of tables of a database in descending order

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

9. To check version 

#psql -V 

All about cron jobs

http://www.thegeekstuff.com/2009/06/15-practical-crontab-examples/

tcp and udp

http://www.diffen.com/difference/TCP_vs_UDP

S3cmd tool

S3cmd sync
=========

 A bit more powerful is sync – the path names handling is the same as was just explained. However the important difference is that sync first checks the list and details of the files already present at the destination, compares with the local files and only uploads the ones that either are not present remotely or have a different size or md5 checksum. If you ran all the above examples you’ll get a similar output to the following one from a sync:

~/demo$ s3cmd sync  ./  s3://s3tools-demo/some/path/
dir2/file2-1.log -> s3://s3tools-demo/some/path/dir2/file2-1.log  [1 of 2]
dir2/file2-2.txt -> s3://s3tools-demo/some/path/dir2/file2-2.txt  [2 of 2]

As you can see only the files that we haven’t uploaded yet, that is those from dir2, were now sync‘ed. Now modify for instance dir1/file1-2.txt and see what happens. In this run we’ll first check with —dry-run to see what would be uploaded. We’ll also add —delete-removed to get a list of files that exist remotely but are no longer present locally (or perhaps just have different names here):

~/demo$ s3cmd sync --dry-run --delete-removed ~/demo/ s3://s3tools-demo/some/path/
delete: s3://s3tools-demo/some/path/file1-1.txt
delete: s3://s3tools-demo/some/path/file1-2.txt
upload: ~/demo/dir1/file1-2.txt -> s3://s3tools-demo/some/path/dir1/file1-2.txt
WARNING: Exiting now because of --dry-run

So there are two files to delete – they’re those that were uploaded without dir1/ prefix in one of the previous examples. And also one file to be uploaded — dir1/file1-2.txt, the file that we’ve just modified.

Sometimes you don’t want to compare checksums and sizes of the remote vs local files and only want to upload those that are new. For that use the —skip-existing option:

~/demo$ s3cmd sync --dry-run --skip-existing --delete-removed ~/demo/ 
              s3://s3tools-demo/some/path/
delete: s3://s3tools-demo/some/path/file1-1.txt
delete: s3://s3tools-demo/some/path/file1-2.txt
WARNING: Exiting now because of --dry-run

See? Nothing to upload in this case because dir1/file1-2.txt already exists in S3. With a different content, indeed, but --skip-existing only checks for the file presence, not the content.

http://s3tools.org/s3cmd-howto

https://github.com/s3tools/s3cmd



Bucket policy example

##############################################################

{
"Id": "Policy1416407058443",
"Statement": [

  {
   "Sid": "Stmt1416407056513",
   "Action": "s3:*",
   "Effect": "Allow",
   "Resource": "arn:aws:s3:::phab-server/*",
   "Principal": {
    "AWS": [
     "*"
    ]
   }
  }

]
}

##############################################################

Puppet - install and configuration

https://docs.puppetlabs.com/
https://www.digitalocean.com/community/tutorials/how-to-install-puppet-to-manage-your-server-infrastructure


Foreman to manage Puppet Nodes

How to optimize php

http://www.radinks.com/upload/config.php

http://www.cyberciti.biz/faq/apache-limiting-upload-size/

sed command

http://www.grymoire.com/unix/sed.html