Skip to main content

Creating A Drupal Database

The following SQL will create a PostgreSQL database for Drupal. I'm calling it drupal in this case, but you can call it whatever you want.

CREATE DATABASE drupal
 WITH OWNER = drupalweb
      ENCODING = 'UTF8'
      TABLESPACE = pg_default
      LC_COLLATE = 'en_US.UTF-8'
      LC_CTYPE = 'en_US.UTF-8'
      CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE drupal TO public;
GRANT ALL ON DATABASE drupal TO drupalweb;

Uptime vs Availability

Do we need to sacrifice system uptime against system availability. And I use the term system availability to identify both uptime and unscheduled outages. So here system availability includes time from both unscheduled and scheduled outages. Why would I ask such a thing? Systems crash for all kinds of reasons: failure in the garbage collector to collect objects or collecting the wrong objects. So why do we need to do regular scheduled reboots of the system?

click to read the rest of the article

Finding the UUID for Linux Drives

UPDATE: I use the term "Drives" here for two reasons. One, people coming from Windows understand a harddisk drive (generally C: or another "device" letter). Two, this is specific to harddrives and not other devices in Linux (like the terminal, printer or scanner).

The Universally Unique Identifier (UUID) is a good way to mount drives under Linux with SATA drives (or any drives, PATA or IDE). This ensures that when you have more than one harddrive, it always gets mounted to the same location specified in /etc/fstab. The reason is that SATA drives are mounted when they're found and on one reboot can come up as /dev/sda and the next time as /dev/sdb.

So in order to set up your file system table correctly, you should specify the UUID instead of the physcial device. So your /etc/fstab will look like this:

Ok, so if you want to use the UUID, how can you find it? We're assuming the drive is already mounted, of course!

One way is to use

$ ls -l /dev/disk/by-uuid
lrwxrwxrwx 1 root root 10 11. Okt 18:02 485554c3-e681-4254-9344-6f038c826fb8 -> ../../sda2

This will list all the devices by UUID and tell you where they're currently mounted. Another would be to use the blkid command.

# blkid /dev/sda1
/dev/sda1: LABEL="/" UUID="485554c3-e681-4254-9344-6f038c826fb8" SEC_TYPE="ext2" TYPE="ext3"

One problem with this is that the blkid command can only list one device at a time. A secondary problem is that the command blkid has to be run as the root user.

Additional information can be found by googling Linux and UUID.

PostgreSQL on Windows - Presentation

I just completed a presentation for my local PostgreSQL User Group (the Portland PUG) and gave it last Thursday. I need to add some updates to clarify things and round out some of it, but here it is as I gave it.

Hopefully there are others who will find it useful.

As a side note, there are NOTES on some of the slides to go along with the material being presented.

[http://www.tbruce.com/Postgresql.odp]

I need to fix these URLs...They are not correct going forward.

Shrink / Delete a SQL Server Log File

When you want to delete a transaction log file (when you have more than one), you need to do the following steps:

ALTER DATABASE dbname SET RECOVERY SIMPLE
ALTER DATABASE dbname SET RECOVERY FULL

or

USE TimsDatabase
BACKUP LOG FileName WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ( 'TimsDB_log1' )
GO
DBCC SHRINKFILE ( 'TimsDB_log2' )

GO

ALTER DATABASE TimsDB REMOVE FILE TimsDB_log2

That should then delete the log file. Don't forget to do a complete backup afterwards.

USE Master
GO
DECLARE @DEST AS VARCHAR ( 200 )
SELECT @DEST = 'Z:\SQL\Backup\TimsDB\TimsDB-' + CONVERT ( CHAR ( 10 ), GetDate(), 120 ) + '.BAK'
BACKUP DATABASE TimsDB TO DISK = @DEST
GO

That gives you a complete backup for additions to the Transaction Log.

There is a word of warning over this - There are not many times you would really want to shrink a Transaction Log. SQL Server expands it as it's needed. The only REAL time I've found it necessary is when there was a SIGNIFICANT amount of growth due to some special processing that was being done. This isn't something you would normally want to do as it does have a negative impact to performance when SQL Server has to expand the Transaction Log.

Table Size in Microsoft SQL Server

Sometimes I want to find out how many rows are in each user table in a SQL Server 2000 database. The following SQL helps me determine this.

SELECT
   'SELECT COUNT(*) AS ' ''' +
   RTRIM ( name ) + ''' ' +
   ' FROM ' + RTRIM ( name )
FROM
   sysobjects
WHERE
   type = 'U'
ORDER BY
   name;

Note: Running this on very large tables or in a database with lots of tables will have a significant negative impact on server performance. This forces a table scan of each table (reading each record in the table), which can really impact performance including flushing various system and database caches.

You can also directly query the statistics, but this is only an approximation and depends on how recently the statistics were updated (with DBCC UPDATE STATISTICS). Looking at the sysindexes table (again, this is SQL 2000), you can look at the rowcnt field and match the id field up against the sysobjects table. The following SQL may provide a good approximation of the rows in each user table.

SELECT
   o.name,
   i.name,
   i.rowcnt
FROM
   sysobjects o,
   sysidexes i
WHERE
   i.id = o.id AND
   o.type = 'U' -- User Tables ;

I have some better SQL I'll have to track down and add to this at some later date to show additional information. And of course, in SQL Server 2005 and SQL Server 2008 there are various Dynamic Management Views (DMVs) you can use to pull related data along with this information.

Copying Files - Preserving Attributes

One way to do this is:

Supposedly, the following will work to copy a file (or group of files) from a directory and preserve the attributes (ownership and permissions).

$ cd /path/to/source/dir

$ find . | cpio -pdumv /path/to/destination/dir

The cpio command is a copy command designed to copy files into and out of a cpio or tar archive, automatically preserving permissions, times, and ownership of files and subdirectories.

An alternative is to use the -p option on the cp command.

The -p option of the cp command can be used to preserve attributes.

See cp --help or man cp for further details.

Of course, don't forget to add the -r on as well, so it copies all files / directories recursively. So you'd use something like:

cp -rp /home/timbruce/data/ /media/backupdrive/timsdata/

Copying files - preserving attributes

One way to do this is:

Supposedly, the following will work to copy a file (or group of files) from a directory and preserve the attributes (ownership and permissions).

$ cd /path/to/source/dir
$ find . | cpio -pdumv /path/to/destination/dir

The cpio command is a copy command designed to copy files into and out of a cpio or tar archive, automatically preserving permissions, times, and ownership of files and subdirectories.

An alternative is to use the "-p" option on the cp command.

See "cp --help" or "man cp" for further details.

Of course, don't forget to add the "-r" on as well, so it copies all files / directories recursively. So you'd use something like:

cp -rp /home/timbruce/data/ /media/backupdrive/timsdata/

Default Route on OpenWRT

When I create a new linux virtual system and use OpenWRT at home to route out to the internet, I need to add a default route so that I can get out to the Internet. For some versions of linux, it doesn't do it automatically for me (or maybe it's the OpenWRT router and something I've misconfigured as it works for some systems, but not others?). The command to do this is:

route add default gw 192.168.8.8

Then to delete the route on one of the Virtual Machines, like pc036, I can use the command route delete, like this:

route delete default gw 192.168.230.11