Ronald Pringadi's Technical Notes

Category: Database

  • Calling a mysql query from linux or dos command line

    There are times when you want to call a mysql command from the bash or dos script then call this script in a scheduler (cron job).
    The example below will show you how to do so.

    mysql -uYOURUSERNAME -pYOURPASSWORD -DYOURDATABASE -e"CALL YOURSTOREDPROCEDURE('YOUR_SP_PARAMETER')"

    Explanation:
    -u is where you put your username, you can either put a space or no space at all after the -u (both works). As you can see I prefer not to put any space.
    -p is where you put your password.
    -D is your database name. That’s a capital D.
    -e this is where your query will go. It needs to be quoted (“). If it’s calling a stored procedure or stored function, you’ll need to use the CALL keyword, otherwise if it’s just a simple query don use the CALL keyword.

  • MySQL Backup and Restore – Using Command Line

    Here is how you backup database using command line. The first line is to backup and the second line is to restore.
    These commands work on both Windows and Linux. In Windows you might want to setup mysql bin directory the environment PATH, you can do this easily by pressing Window + Break > Advanced System Settings > Environment > PATH. Then run the cmd or the command prompt. If you already open a command prompt before you setup the PATH, you need to restart your command prompt

    MySql doesn’t export stored procedure/function by default. It also doesn’t export triggers. If you need those, the you’ll need to add “–routines –triggers” to your command. Even if you don’t use stored procedure, function, or triggers it is safer if you issue your backup with those option enabled.


    $ mysqldump -uusername -ppassword --routines --triggers --lock-tables=false database_name > YOURSQLFILE.sql
    $ mysql -uusername -ppassword database_name < YOURSQLFILE.sql

    If you need to backup all database. The user that we usually use to backup all database is "root". This will backup all triggers, and functions. You will need to pre-create other users that has certain privilege over stored procedure or functions.

    mysqldump -uusername -ppassword --opt --events --all-databases > alldb.sql
    mysql -uusername -ppassword < alldb.sql

  • Vacuum the Whole Database in Postgres

    Postgres comes with a functionality called vacuum. Vacuum is intended to cleanup dead tuples or rows.
    This is how you cleanup the entire DB in PostgreSQL


    /usr/pg9/bin/vacuumdb --full --port=5433 --username=YOUR_USERNAME --password DB_NAME

  • Backup and Restore Postgres Using pg_dump and psql

    This is how you backup your Postgres Database

    To Backup

    pg_dump --host=localhost --port=5432 --username=postgres --file=YOUR_FILENAME.sql YOUR_DB_NAME

    There are other options such as if you’re interested only on the structure then you can add:

    pg_dump --host=localhost --port=5432 --username=postgres --schema-only --format=p --create --inserts --file=YOUR_FILENAME.sql YOUR_DB_NAME

    To Restore

    psql -Upostgres -hlocalhost YOUR_DB_NAME < YOUR_FILENAME.sql

  • Setting Session Authorization and Search Path

    SET SESSION AUTHORIZATION jane;
    SET search_path = schema1,schema2, schema3;
    SELECT * FROM any_table_in_schema_1_2_or_3;

  • Implicit Casting in PostgreSQL

    CREATE CAST(integer AS character varying) WITH INOUT AS IMPLICIT;
    CREATE CAST (character varying AS integer) WITH INOUT AS IMPLICIT;

  • Integer Array Casting in PostgreSQL


    -- Rule: "_DELETE" ON sometable
    -- DROP RULE "_DELETE" ON sometable;

    CREATE OR REPLACE RULE "_DELETE" AS
    ON DELETE TO sometable DO INSTEAD DELETE FROM _sometable
    WHERE _sometable.account_id = old.account_id AND (old.domain_id = ANY ((( SELECT get_visible_domains('DELETE'::text) AS get_visible_domains)::integer[])));

  • Tracing PostgreSQL Error Log

    /pgsql/dev-db/tail -f postgresql.log

  • Reset Sequence using PostgreSQL

    SELECT setval('YOUR_SEQUENCE_NAME', (SELECT MAX(id) FROM YOUR_TABLE_NAME));