To get the current id value connect to database and use the following query //get current id <?php $result = pg_exec($conn, “SELECT currval(‘table_id_seq’) AS id”); $id = pg_result($result, 0, “id”); //free result pg_freeresult($result); ?>
To password protect a folder using a postgreSQL table use pg_auth. This goes in the .htaccess file: Auth_PG_host localhost Auth_PG_port 5432 Auth_PG_database isp Auth_PG_pwd_table clients Auth_PG_uid_field username Auth_PG_pwd_field password AuthName “IggyISP Admin” Auth_PG_encrypted off AuthType Basic require valid-user This goes in the website virtual host block before the : <Directory /var/www/html/isp/secure/> AllowOverride AuthConfig order allow,deny [...]
To select a lowercase variation of a particular field use the following query SELECT * FROM table WHERE field=’$var’ OR lower(field)=’$var’
This was documented for FreeBSD. The file is named: 010.pgsql.sh The file is located: /usr/local/etc/rc.d #!/bin/sh # $FreeBSD: ports/databases/postgresql7/files/pgsql.sh.tmpl # v 1.16 2003/12/04 12:00:49 seanc Exp $ # # For postmaster startup options, edit $PGDATA/postgresql.conf # # Note that PGDATA is set in ~pgsql/.profile, # don”t try to manipulate it here! PREFIX=/usr/local PGBIN=${PREFIX}/bin LOGFILE=/var/log/pgsql OPTIONS=”-i” [...]
Make sure backup folder is owned by pgsql or mysql then adjust paths to pg_dumpall, mysqldump, and gzip to fit your system. PostgreSQL # vacuum dbase – nightly at 3:30am 30 2 * * * pgsql /usr/local/pgsql/bin/vacuumdb -a -z # daily/weekly/monthly dumps 1 3 * * * pgsql /usr/local/pgsql/bin/pg_dumpall | /usr/bin/gzip > /usr/local/pgsql/backups/master_daily.gz 15 4 [...]
This was a challenge to figure out. This query deletes all rows that are more than one day old and has a status of blank or null. DELETE FROM table WHERE (($field_date_added < now() – ”1 days”::interval) AND ($field_status=”” OR $field_status IS NULL))
To convert the PostgreSQL date into a usable date: <?php function pg_date($pgtimestamp){ $Date_Time = explode(” “, $pgtimestamp); $date_pieces = explode(“-”, $Date_Time[0]); // strip the tz offset from time $time_convert = explode(“-”, $Date_Time[1]); $time_pieces = explode(“:”, $time_convert[0]); $timestamp = mktime($time_pieces[0], $time_pieces[1], $time_pieces[2], $date_pieces[1], $date_pieces[2], $date_pieces[0]); return date(“F j, Y, g:i a”, $timestamp); } //end pg_date ?>
Allowing PostgreSQL TCP/IP Connections The first thing you need to do is alter the startup script for postgresql so that it allows tcp/ip connections. Do the following: >ee /usr/local/psa/rc.d/postgresql BELOW: PGDATA=${PREFIX}/data ADD: OPTIONS=”-i” CHANGE the export line in start_pg to: “export PGDATA=${PGDATA} && exec /usr/local/psa/postgresql/bin/pg_ctl start -s -o ${OPTIONS} -l /var/log/pgsql” Start and stop PostgreSQL [...]
Continue reading about Getting PostgreSQL to play nice with Plesk
In some cases you will want to change the last value for a sequence. You can do this by updating the current value of the sequence. SELECT setval(‘table_id_seq’, 123)
Dump a database from the command line: >/path/to/pg_dump database_name pg_dump_file.sql Restore the database from the command line: >/path/to/psql -U username -d database_name -f pg_dump_file.sql