How to install DBD::Pg Perl module on Mac OSX

Make sure `pg_config` is in your path, and that's it! DBD::Pg will build normally.

Quick guide: How to set up MySQL and PostgreSQL databases for Perl development

# The following means mysql is not running:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

MySQL

INSTALL

sudo apt install -y libmysqlclient-dev mysql-client mysql-server

START

$ sudo service mysql start

SETUP

CREATE USER zaphod;

ALTER USER 'zaphod'@'%' IDENTIFIED BY 'some_pass';

GRANT ALL PRIVILEGES ON *.* TO 'zaphod'@'%'; -- makes zaphod an admin

FLUSH PRIVILEGES;

ACCESS

$ mysql -u zaphod -D some_db -p

ADMIN ACCESS

$ sudo mysql

PostgreSQL

INSTALL

sudo apt install -y postgresql libpq-dev

START

$ sudo service postgresql start

SETUP

create user zaphod;

\password zaphod

create database some_db;

grant all privileges on database some_db to zaphod;

ACCESS

$ psql -U zaphod -h localhost -d some_db

# specifying the host forces md5 (password) authentication. Otherwise default is "peer"

ADMIN ACCESS

$ sudo su postgres

$ psql

Perl Postgres test fixtures

Test::PostgreSQL approach:

Test::DBIx::Class approach:

Install postgres on Ubuntu 16

How to do it:

• sudo apt-get install postgresql postgresql-contrib
• sudo -i -u postgres
• psql

(source)

When Postgres allows any password

Problem: Postgres does not check my password, i.e. it accepts all passwords.

Solution: pg_hba.conf and change trust to md5. Restart postgres. That is all.

(source)

Lock rows with DBIx::Class

$schema->txn_do(sub{

    $foos_rs->search({}, {for => 'update'})->all; # Lock rows

    # Check status of something

    # Update it
});

(source, source, source)

Faster count(*) in postgres

For an approximate count you can do:

SELECT reltuples FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;

(replacing schema_name and table_name)

(source)

Find and kill slow postgres queries

SELECT query_start,datname,procpid,current_query FROM pg_stat_activity;
SELECT NOW();

SELECT pg_cancel_backend($PID);
-- where $PID is the number from the procpid column in the first query

Display postgres enum values

• To see all enums:

select n.nspname as enum_schema,  
    t.typname as enum_name,
    string_agg(e.enumlabel, ', ') as enum_value
from pg_type t 
    join pg_enum e on t.oid = e.enumtypid  
    join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by enum_schema, enum_name;

thanks, StackOverflow

• To see one enum:

SELECT enumlabel  FROM pg_enum WHERE enumtypid = 'your_enum_here'::regtype;

Use variables with Postgres, just like MySQL

It's a little more difficult than MySQL, as you have to create a function to contain the logic:

DROP FUNCTION get_column(integer);
CREATE FUNCTION get_column(row_id integer) RETURNS text AS $$
DECLARE
    t1_row foo%ROWTYPE;
BEGIN
    SELECT * INTO t1_row FROM foo WHERE foo.status != 'closed' limit 1;
    RETURN t1_row;
END;
$$ LANGUAGE plpgsql;

SELECT get_column(3);

References: Stackoverflow, Stackoverflow again, the Postgres docs

Find all tables with a particular column

Postgres:

SELECT table_name, column_name FROM information_schema.columns WHERE column_name like '%foo%';

information_schema is documented here:

http://www.postgresql.org/docs/devel/static/information-schema.html

DBI connection code

use DBI;

my $dsn = "dbi:Pg:dbname=xxxxxx";
my $dbh = DBI->connect($dsn, 'postgres', '');
                                                                               
my $statuses = $dbh->selectall_hashref("SELECT id, status, name FROM statuses", "id");

View current queries in postgres

Edit config

sudo vim /var/lib/pgsql/9.0/data/postgresql.conf

Add this line:

stats_command_string = true

Restart

pg_ctl reload

Then

SELECT datname,procpid,current_query FROM pg_stat_activity

Thanks

PostgreSQL basics (for MySQL users)

Installing

• sudo apt update
• sudo apt install postgresql postgresql-contrib
• sudo service postgresql start
• sudo passwd postgres # then close and re-open the terminal(?)
• sudo -u postgres psql
• create user foo;
• alter user foo with superuser;
• alter user foo with password 'new_password';
• psql -Ufoo -d postgres

Reconfigure authentication if necessary to either require or disable passwords.

(source)

Using

MySQL and Postgres command equivalents (mysql vs psql)

connect: psql -U [username, e.g. postgres] -d [database]

• \c dbname = connect to dbname
• \l = list databases:
• \dt = describe tables, views and sequences
• \dt+ = describe tables with comments and sizes 
• \dT = describe Types
• \di = describe indexes
• \q = quit
• \x = toggle equivalent of adding MySQL's \G at the end of queries to display columns as rows 
• \connect database = change to a different database

• CREATE DATABASE yourdbname;
• CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
• GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

• Use auto_increment in a column definition:
• create sequence foo__id__seq increment by 1 no maxvalue no minvalue start with 1 cache 1; 
• create table foo ( id integer primary key default nextval('foo__id__seq') );
• Reset an auto_increment counter (sequence):
• SELECT setval('sequence_name', 1, false); -- this works
• ALTER SEQUENCE sequence_name RESTART WITH 1; -- this also works
• See what's in an ENUM
• SELECT enumlabel  FROM pg_enum WHERE enumtypid = 'myenum'::regtype ORDER BY id;
• Closest equivalent of MySQL's "show create table"
• pg_dump -U postgres --schema-only [database] >> dump.sql
• See current activity
• SELECT * FROM pg_stat_activity (equivalent to MySQL's "show processlist")
• Drop all current connections
• SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='foo'; # foo = database name

Other stuff

• pg_dump -Upostgres -hHOSTNAME DBNAME -fOUTPUTFILE.sql --no-password
• psql -Upostgres -hHOSTNAME -dDBNAME -fINPUTFILE.sql
• psql -Upostgres -hHOSTNAME -dDBNAME -c "Some SQL command"

See also