Category: database

PostgreSQL commands to remember

List of commands to remember using postgres DB managment system.

Login, Create user and password

# login to psql client
psql postgres # OR
psql -U postgres
create database mydb; # create db
create user abhilash with SUPERUSER CREATEDB CREATEROLE encrypted password 'abhilashPass!'; 
grant all privileges on database mydb to myuser; # add privileges

Connect to DB, List tables and users, functions, views, schema

\l # lists all the databases
\c dbname # connect to db
\dt # show tables
\d table_name # Describe a table
\dn # List available schema
\df #  List available functions
\dS [your_table_name] # List triggers
\dv # List available views
\du # lists all user accounts and roles 
\du+ # is the extended version which shows even more information.

Show history, save to file, edit using editor, execution time, help

SELECT version(); # version of psql
\g  # Execute the previous command
\s # Command history
\s filename # save Command history to a file
\i filename # Execute psql commands from a file
\? # help on psql commands
\h ALTER TABLE # To get help on specific PostgreSQL statement
\timing #  Turn on/off query execution time
\e # Edit command in your own editor
\e [function_name] # It is more useful when you edit a function in the editor. Do \df for functions
\o [file_name] # send all next query results to file
    \o out.txt
    \dt 
    \o # switch
    \dt

Change output, Quit psql

# Switch output options
\a command switches from aligned to non-aligned column output.
\H command formats the output to HTML format.
\q # quit psql

Reference: https://www.postgresqltutorial.com/postgresql-administration/psql-commands/

Liferay 7.3: Create custom database services (service-builder)

STEP 1:

Open the IDE. Goto File -> New -> Liferay Module Project


Select `service-builder` as Template

Click Next. Provide the package name and click finish

After that you can see two folders are created (*-api and *-service) inside your workspace.

And three folders in the IDE

Open siteService-service and click on service.xml . Click on the Entities and delete the default Foo column

And then add the Entity named Site . It is just an Entity, that connects to the table.

Click on the Site Entity and provide the table name

Add the Table Name

Add the columns as many as you want.

Select the column type from here:

Click on the Source Tab and you can see in the service.xml details of all columns that added.

Double click on the buildService to build the new service and double click on the deploy to deploy the service.

Now click on the down arrow and gradle -> refresh project. You can see the bundles created.

And the .jar bundles inside the osgi modules

Copy this *-api.jar file into the deploy folder of the server.

~/liferay-ce-portal-tomcat-7.3.0-ga1-20200127150653953/liferay-ce-portal-7.3.0-ga1/deploy

and then copy the *-service.jar into the same folder

You can see these are processing and started in the server logs.

INFO  [com.liferay.portal.kernel.deploy.auto.AutoDeployScanner][AutoDeployDir:263] Processing sitesService.api.jar

~/liferay-ce-portal-tomcat-7.3.0-ga1-20200127150653953/liferay-ce-portal-7.3.0-ga1/osgi/modules][BundleStartStopLogger:39] STARTED sitesService.api_1.0.0 [1115]

[com.liferay.portal.kernel.deploy.auto.AutoDeployScanner][AutoDeployDir:263] Processing sitesService.service.jar

~/liferay-ce-portal-tomcat-7.3.0-ga1-20200127150653953/liferay-ce-portal-7.3.0-ga1/osgi/modules][BundleStartStopLogger:39] STARTED sitesService.service_1.0.0 [1116]

Now check the database, if the Site_ table with all columns are created or not

You can see the table and columns are created. In the next topic we discuss about adding services to this service builder.

Backup your system databases using Ruby backup gem

Install RVM (Or Rbenv) to manage your Ruby versions

 $ gpg --keyserver hkp://keys.gnupg.net --recv-keys 409B6B1796C275462A1703113804BB82D39DC0E3 7D2BAF1CF37B13E2069D6956105BD0E739499BDB
 $ curl -sSL https://get.rvm.io | bash

Restart Terminal and type rvm -v

 $ rvm install 2.5
 $ rvm gemset create backup
 $ rvm gemset use backup
 $ gem install backup
 $ backup generate:model --trigger project_2_backup --archives --storages='s3' --compressor='gzip' --notifiers='mail' 
 Generated configuration file: '/home/ubuntu/Backup/config.rb'.
 Generated model file: '/home/ubuntu/Backup/models/project_2_backup.rb'.
 Usage:
   backup generate:model --trigger=TRIGGER
 Options:
   --trigger=TRIGGER
   [--config-path=CONFIG_PATH]  # Path to your Backup configuration directory
   [--databases=DATABASES]      # (mongodb, mysql, postgresql, redis, riak)
   [--storages=STORAGES]        # (cloudfiles, dropbox, ftp, local, ninefold, rsync, s3, scp, sftp)
   [--syncers=SYNCERS]          # (cloud_files, rsync_local, rsync_pull, rsync_push, s3)
   [--encryptors=ENCRYPTORS]    # (gpg, openssl)
   [--compressors=COMPRESSORS]  # (bzip2, gzip, lzma, pbzip2)
   [--notifiers=NOTIFIERS]      # (campfire, hipchat, mail, presently, prowl, twitter)
   [--archives]
   [--splitter]                 # use `--no-splitter` to disable
                               # Default: true

Sample Model File

Add the following conf in Backup/models/project_2_backup.rb:

Example for mongodb

 database MongoDB do |db|
     db.name               = "db_name"
     db.username           = "db_username"
     db.password           = "db_pswd"
     db.host               = "localhost"
     db.port               = 27017 
     db.ipv6               = false
     #db.only_collections   = ["only", "these", "collections"]
     db.additional_options = ['--authenticationDatabase=admin']
     db.lock               = false
     db.oplog              = false
   end
   ## 
   # Amazon Simple Storage Service [Storage]
   #
   store_with S3 do |s3|
     # AWS Credentials
     s3.access_key_id     = "YOUR_ACCESS_KEY"
     s3.secret_access_key = "YOUR_SECRET_KEY"
     # Or, to use a IAM Profile:
     # s3.use_iam_profile = true
     s3.region            = "ap-southeast-2" 
     s3.bucket            = "bucket_name"
     s3.path              = "bucket_name_path"
     s3.keep              = 12
     # s3.keep              = Time.now - 2592000 # Remove all backups older than 1 month.
   end 

 # Notification mail infos
 notify_by Mail do |mail|
     mail.on_success           = true
     mail.on_warning           = true
     mail.on_failure           = true
     mail.from                 = "_____@gmail.com"
     mail.to                   = "____@___.com"
     mail.cc                   = "______@_____.com, _____@______.com"
     #mail.bcc                  = "bcc@email.com"
     #mail.reply_to             = "reply_to@email.com" 
     mail.address              = "smtp.gmail.com"
     mail.port                 = 587
     mail.domain               = "domain_name"
     mail.user_name            = "email_username"
     mail.password             = "email_password"
     mail.authentication       = "plain"
     mail.encryption           = :starttls
 end

Once you’ve setup your configuration, check your work with:

$ backup check

If there are no errors, the check should report:

[2019/03/28 10:02:26][info] Configuration Check Succeeded.

Perform Backup:

$ backup perform --trigger project_2_backup

The Keep Option

keep a specified number of backups in storage. After each backup is performed, it will remove older backup package files based on the keep setting.

keep as a Number

If a number has been specified and once the keep limit has been reached, the oldest backup will be removed.

Note that if keep is set to 5, then the 6th backup will be transferred and stored, before the oldest is removed. So be sure you have space available for keep + 1 backups

keep as Time

When a Time object is set to keep it will keep backups until that time. Everything older than the set time will be removed.

Enable MongoDB Access Control

The default data directory for MongoDB is /data/db

This can be overridden by a dbpath option specified on the command line or in a configuration file.

If you install MongoDB via a package manager such as Homebrew or MacPorts these installs typically create a default data directory other than /data/db and set the dbpath in a configuration file.

You can check the dbpath by:

db.serverCmdLineOpts()

in your mongo shell

 "storage" : {
    "dbPath" : "/usr/local/var/mongodb"
 },

The following procedure first adds a user administrator to a MongoDB instance running without access control and then enables access control.

1.  Start MongoDB without access control.

$ mongod --port 27017 --dbpath /data/db1

2. Connect to the instance.

$ mongo --port 27017

3. Create the user administrator.

In the admin database, add a user with the userAdminAnyDatabase role. For example, the following creates the user myUserAdmin in the admin database:

Note: The database where you create the user (in this example, admin) is the user’s authentication database.

 > db.createUser(
 ...   {
 ...     user: "abhilash",
 ...     pwd: “password!“,
 ...     roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
 ...   }
 ... ) 

 Successfully added user: {
 "user" : "abhilash",
 "roles" : [
  {
    "role" : "userAdminAnyDatabase",
    "db" : "admin"
  }
 ]
 }

Disconnect the mongo shell.

4. Re-start the MongoDB instance with access control.

$ mongod --auth --port 27017 --dbpath /usr/local/var/mongodb/db1

Clients that connect to this instance must now authenticate themselves as a MongoDB user.

 > db.auth();
 Error: auth expects either (username, password) or ({ user: username, pwd: password })
 0
 > db
 test

** To authenticate during connection:

$ mongo --port 27017 -u "abhilash" -p "password!” --authenticationDatabase "admin"

** To authenticate after connecting

Connect the mongo shell

 $ mongo
 > use admin
 > db.auth("abhilash", “password!“ )
 > mongo
 MongoDB shell version v3.4.7
 connecting to: mongodb://127.0.0.1:27017
 MongoDB server version: 3.4.7
 Server has startup warnings:
 2018-01-22T10:32:18.027+0530 I CONTROL  [initandlisten]
 2018-01-22T10:32:18.027+0530 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
 2018-01-22T10:32:18.027+0530 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
 2018-01-22T10:32:18.027+0530 I CONTROL  [initandlisten]
 > use admin
 switched to db admin
 > db
 admin
 > db.auth("abhilash", "password!”);
 1
 > use my_dbname;
 > db.createUser(
   {
     user: "vadmin",
     pwd: “pass111!”,
     roles: [ { role: "readWrite", db: "my_dbname" },
              { role: "read", db: "test" } ]
   }
 )

Reference:  Mongodb enable-authentication 

Mongodb how to Import / Export in Linux/Mac

For Importing a mongodb use the following command

$ mongodump --db database_name

This will dump the json/bson files into dump/db_name folder
Or specify a directory with -o option

$ mongodump --db database_name -o path_to_folder

By specifying username and password

$ mongodump --db database_name -o /path/to/folder/ --username=my_user --password="my_password"

For Exporting a mongodb use the following command

$  mongorestore --db database_name path_to_the_json_bson_files

path_to_the_json_bson_files => That we already imported and stored before.

Import one document

$ mongodump --db=db_name --collection=collection_name --out=path_to_folder_to_import
$ mongorestore --db=new_db_name --collection=collection_name path_to_folder_to_import/db_name/collection_name.bson

PostgreSQL 9.3 : Installation on ubuntu 14.04

Hi guys, I just started installing postgres on my ubuntu VM. I referred some docs, and followed this one: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

Its pretty much explained in this page. But just explaining here the important things.

You can install postgres by ubuntu’s own apt packaging system. Update local apt repository.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Postgres uses role based access for the unix users. After the installation a default role called ‘postgres’ will be created. You can login to postgres account and start using or creating new roles with Postgres.

Sign in as postgres user

$ sudo -i -u postgres

Access the postgres console by

$ psql

But i cannot enter into the console and I got the following error:

postgres@8930a29k5d05:/home/rails/my_project$ psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What could be the reason for this error?

So just gone through Postgres doc (http://www.postgresql.org/docs/9.3/static/server-start.html). You can see the same error under the section 17.3.2. Client Connection Problems. But the solution is not mentioned.

Original Reason: PostgreSQL Server was not running after the installation.

I tried rebooting the system and via init script the server should run automatically. But the server is not running again. I understood that something prevents postgres from running the server. What is it?

Just check your postgres server is running or not

$ sudo -aux | grep post
postgres@8930a29k5d05:/home/rails/my_project$ ps -aux | grep postgres
root       136  0.0  0.2  47124  3056 ?        S    06:10   0:00 sudo -u postgres -s
postgres   137  0.0  0.3  18164  3220 ?        S    06:10   0:00 /bin/bash
postgres   140  0.0  0.2  15572  2192 ?        R+   06:10   0:00 ps -aux
postgres   141  0.0  0.0   4892   336 ?        R+   06:10   0:00 grep post

The server is not running.

Run the server manually by

root@8930a29k5d05:/home/rails/my_project#  /etc/init.d/postgresql start
 * Starting PostgreSQL 9.3 database server
                                                                                                                                                         [ OK ] 
root@8930a29k5d05:/home/rails/my_project# ps aux | grep postgres
postgres   158  0.1  2.0 244928 20752 ?        S    06:28   0:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres   160  0.0  0.3 244928  3272 ?        Ss   06:28   0:00 postgres: checkpointer process

postgres   161  0.0  0.4 244928  4176 ?        Ss   06:28   0:00 postgres: writer process

postgres   162  0.0  0.3 244928  3272 ?        Ss   06:28   0:00 postgres: wal writer process

postgres   163  0.0  0.5 245652  6000 ?        Ss   06:28   0:00 postgres: autovacuum launcher process

postgres   164  0.0  0.3 100604  3336 ?        Ss   06:28   0:00 postgres: stats collector process

root       178  0.0  0.0   8868   884 ?        S+   06:28   0:00 grep --color=auto post
root@8930a29k5d05:/home/rails/my_project#

Now the server starts running. If still not works, then try to reconfigure your locales as mentioned here

$ dpkg-reconfigure locales

It is strange that, after installing such a popular database software, it doesn’t provide any information regarding the failure of its own server. It should give the developers some clue so that they can save their precious time.

The reason of this failure, what I concluded is
1. After installation we have to run the server manually
OR
2. I tried resetting the locales (So if no locales set in the machine may prevented the postgres from starting automatically?)

How to create a migration file dynamically by meta programming in rails 4.0

If you want to create a migration file from a module written in lib file or somewhere from your ruby file and execute it, use the metaprogramming which can create a class or method dynamically. The following code snippet shows the methods we use and gives a better idea to create migration file dynamically.

def create_columns(tb_with_cols)
    add_columns = ""
    tb_name = tb_with_cols.keys.first
    columns = tb_with_cols.values.first
    columns.each { |c_name, c_type| add_columns << "\tadd_column(':#{tb_name}', :#{c_name}, :#{c_type})\n" }

    add_columns
 end

 def migration_file_content(tb_with_cols)
   cols = create_columns(tb_with_cols)
<<-RUBY
  class AddMissingColumnsToTable < ActiveRecord::Migration
     def change_table
    #{cols}
   end
 end
  RUBY
 end


 def write_content_to_file(path, content)
    File.open(path, 'w+') do |f|
      f.write(content)
    end
 end

Just call the method migration_file_content in your code. Pass the parameter tb_with_cols as a Hash, in which the key is the table_name and value is the columns that should be added to that table. Ex:

tb_with_cols = {:users => {:name => :string, :age => :integer, :address => :text} }
content = migration_file_content(tb_with_cols)
write_content_to_file("#{Rails.root}/db/migrations/', content)

After that call the method write_content_to_file with your new migration file path and the content from our migration_file_content method. 🙂

How to reinstall mongodb in ubuntu linux

Before reinstalling mongodb, in your linux system check whats installed in the system

 $ sudo dpkg -l | grep mongo
 ii  mongodb-org                              2.6.3                                       amd64        MongoDB open source document-oriented database system (metapackage)
 ii  mongodb-org-mongos                       2.6.3                                       amd64        MongoDB sharded cluster query router
 ii  mongodb-org-server                       2.6.3                                       amd64        MongoDB database server
 ii  mongodb-org-shell                        2.6.3                                       amd64        MongoDB shell client
 ii  mongodb-org-tools                        2.6.3                                       amd64        MongoDB tools

Remove all
$ sudo apt-get remove mongodb*

Install mongodb again, check mongodb org

$ sudo apt-get install -y mongodb-org

For particular version

$ sudo apt-get install -y mongodb-org=2.6.5 mongodb-org-server=2.6.5 mongodb-org-shell=2.6.5 mongodb-org-mongos=2.6.5 mongodb-org-tools=2.6.5


check whats newly installed

$ sudo dpkg -l | grep mongo
 ii  mongodb-org                              2.6.8                                       amd64        MongoDB open source document-oriented database system (metapackage)
 ii  mongodb-org-mongos                       2.6.8                                       amd64        MongoDB sharded cluster query router
 ii  mongodb-org-server                       2.6.8                                       amd64        MongoDB database server
 ii  mongodb-org-shell                        2.6.8                                       amd64        MongoDB shell client
 ii  mongodb-org-tools                        2.6.8                                       amd64        MongoDB tools

check mongodb server status

$ sudo service mongod status

Start mongodb server

$ sudo service mongod start

Start mongo shell

$ mongo

check mongodb log file here

 $ tail -n 200 /var/log/mongodb/mongod.log