Some Quick cli Syntax for Postgres

How to Dump SQL result to a text file using cli

> psql -U postgres -d [database_name] -c ‘SELECT * FROM users’

How to exit postgres command-line utility psql
Type \q and then press ENTER to quit psql

Reset Root Password on MySQL Database
Notice that in PostgreSQL superuser is called postres (not root). If you forgot superuser password, you can reset it this way:

edit file pg_hba.conf

> sudo vim /etc/postgresql/9.3/main/pg_hba.conf

and find there a line similar to:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                md5

md5 here means that postgres asks for password. If there is this line then replace
md5 with peer, if there is no this line then add it

local   all             postgres                                peer

save the file, exit and reload postgresql service to pick up the updated configuration:

> sudo service postgresql reload

Then you should be able to get access to psql without providing a password this way:

> sudo -u postgres psql

In psql you can change user password using this command:

ALTER USER postgres PASSWORD 'new password';

PostgreSQL performance and monitoring

switch current database

\connect database_name or \c database_name

you can user autocomplete by pressing [tab]

list tables in current database

\dt

create a new database and import tables, data from a dump
in psql

CREATE DATABASE some_database

create a dump (in terminal)

> pg_dump dbname > outfile

You may notice that no password is used. That is because with Postgres you can setup a password file that is used to verify your access as such:

> vim ~/.pgpass

Then (format is hostname:port:database:username:password):

localhost:5432:mydbname:postgres:mypass
[ctrl-x] then confirm with y, then hit [enter]

save and exit

> chmod 600 ~/.pgpass

restore a dump (in terminal)

> psql dbname < infile more details on dump-restore

create a user (it sql)

CREATE USER tester WITH PASSWORD 'test_password';
GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;

notice difference in quotation marks – in first case it must be single quotation mark ‘ and in the second case – double quotation mark “, otherwise PostgreSQL raises a syntax error

restart PostgreSQL

> sudo service postgresql restart

other regular service actions available too – [force-reload, reload, start, status, stop]

Also for Nginx (unrelated to this post really), Restart Nginx

sudo service nginx restart
other regular service actions available too – [force-reload, reload, start, status, stop]
reload only reloads configuration files, without stopping a service (which restart does)

Related: How to Dump SQL Result in MySQL to a Text File Using cliReset Root Password on MySQL DatabaseSome MySQL cli Syntax

Chrome Remote Desktop

I am more often frustrated by Google the last few years that pleased with them. But they do still provide some pretty awesome tools. For example, Chrome Remote Desktop lets you access a computer over the internet (and lets you to allow another user to access your computer securely over the internet).

Chrome Remote Desktop allows users to remotely access another computer through Chrome browser or a Chromebook. Computers can be made available on an short-term basis for scenarios such as ad hoc remote support, or on a more long-term basis for remote access to your applications and files.

screen shot of chrome remote desktop

Chrome Remote Desktop is fully cross-platform. Provide remote assistance to Windows, Mac and Linux users, or access your Windows (XP and above) and Mac (OS X 10.6 and above) desktops at any time, all from the Chrome browser on virtually any device, including Chromebooks (including Android phones and iPhones). The iPhone app is new.

Some users worry about installing such an app given all the spying and hacking scandals. That is not a completely crazy worry. Google, and others, have been taking advantage of weak user control (and even bugs and work arounds to avoid stated user preferences) to track users and use that information to make money selling ads. With many cool and useful tools there are risk of them being misused. And practices of governments and huge corporations have been so egregious to give a sensible person pause. Still in the right situations this is a pretty cool looking tool (similar things exist but the combination of price [this being free] and simplicity make this interesting).

Related: Chrome Remote Desktop support forumUsing scp (secure copy) to Copy Files Between ComputersUsing Rsync to copy Files Between Servers and ComputersLinux/Ubuntu File and Directory PermissionsGovernments Shouldn’t Prevent Citizens from Having Secure Software Solutions

Compare WordPress Files on Server to Proper WordPress Version

Sadly one of the hassles in managing your own WordPress blog is dealing with people that use your blog to serve spam content. These hacks can insert spam links into your pages and posts or create spam directories that are completely their own content on your domain.

There are many issues to deal with in re-establishing control of your server; but that isn’t the scope of this post.

This is just a tips if you are troubleshooting to try and determine what is going on. Often your server has been hacked to allow uploaded php pages to be added or for WordPress php files to be edited.

One way to track down if the files have been changed or new ones added is to compare the WordPress files on your server to the current files for a fresh WordPress install. This assumes your blog is using the current version, which hopefully it is because on the big improvement WordPress made is to make those updates automatic. That greatly reduces the chance to have WordPress be the vector to infecting your server. If you were using a older version then just compare to the field for that version from the WordPress server.

If you don’t have a current backup I would make a backup before I tried this. Obviously, don’t make any deletions or changes to your server unless you understand what you are doing. You can create big problems for yourself.

You can use the diff command to view the difference between WordPress on your sever and the fresh install from WordPress. I install the new WordPress in a new directory outside public_html. At the cli on a Ubuntu/Linux server:

sudo wget http://wordpress.org/wordpress-4.0.zip
unzip wordpress-4.0.zip
diff -rq wordpress ../public_html/

wordpress-4.0.zip – replace with whatever the version is you are using.
../public_html/blog/ – replace with the path to your blog

Continue reading

Fix for When a WordPress Blog Stops Displaying Images

My WordPress network site stopped displaying uploaded images. I finally found something that works, though it is a lame “solution” in my opinion.

I found this advice and it worked for me. Insert the following in wp-includes/ms-files.php

adding ob_clean() before the readfile in ms-files.php

So you end up with:

// If we made it this far, just serve the file
ob_clean();
readfile( $file );
?>

Caching seemed to make the images “disappear” slowly (they didn’t all go at once and using different browsers they “disappeared” at different times). So when I fixed it they “reappeared” at different times (it seemed like cached ones stayed disappeared for awhile).

The advice mentions the image files being seen as “corrupted.” The files seem unlikely to actually be “corrupt” because these are images that worked for years and then all of a sudden every image for the sites “disappeared” (wouldn’t be displayed). I suppose it is possible the are corrupt in some detailed way and some update no longer allowed some previous “lax” checking to allow the non-compliant files but it seems more likely that some of thing has gone wrong (but somehow ob_clean takes care of it).

Related: WordPress error: Image could not be processed. Please go back and try again.Overcoming Conflicts Multiple Blog WordPress Network on One ServerBash Profile Adjustments, Scrolling History

Turn It Off and On Again

I had a WordPress blog lose the database connection. As I tried to troubleshoot it I rapidly got to the point of thinking that maybe just rebooting the server would fix things – since no changes had been made that should cause the database connection to be lost. But also I figured I couldn’t be so lucky that such a simple thing would work.

I tried to log into the database using the cli and it was failing. MySQL was still running – according to top. I have automated security updates setup for the VPS server running Ubuntu. My thought now is those updates somehow messed things up to the extent the server database connections somehow wasn’t working. I sure hoped that was the case.

I rebooted and hoped.

After a bit I was able to see that it was working.

When I went to reboot I noticed I hadn’t rebooted in a long time. My guess is I might have the automated security updates setup wrong on this server (maybe it is installing more than just the security updates), I’ll check. I have not had this problem before with other servers, obviously breaking a database in this way would cause lots of problems on production machines so I tend to think it is more likely I messed something up than this is a likely outcome when using automated security updates (but such things are possible which is why I think places with full time system admins and important servers rely on manual updates with professionals watching everything to be sure nothing obvious breaks).

My feeling is the longer you go with not rebooting the server the more likely some issues are to crop up (but also every reboot is more like to result in some broken thing, right then, than doing nothing so it is a tradeoff). And I could be wrong in that feeling, it is just what I guess without much evidence to support my guess.

Anyway I was very happy turning it off and on again worked. Honestly I went ahead and updated the server before I rebooted, but my guess is just turning it off and on again would have worked.

Related: Making Sure You Don’t Run Out of Space on Your VPSBasic MySQL Performance MonitoringWordPress error: Image could not be processed. Please go back and try again.

Cleaning the Screen of Your Mac Air or Mac Powerbook

My Mac Air screen can get dirty and I am a bit worried about cleaning it due to the warnings that using various cleaning products of methods can damage your screen. From what I have been able to determine the best strategy is just using water and a microfiber fabric. I use one microfiber which I get wet with water and clean the screen. Then I dry it off with another microfiber. Using chemicals can damage the screen. And using even paper towels can result in scratches on the screen.

I have had very good success with just water and the microfiber cloth. I imagine water and microfiber cloth is the best way to clean an iPad (when kids and cats play with it) but I haven’t tried it myself.

From Apple’s web site:

The materials used to make Apple products vary; in some cases each product may have specific cleaning requirements, which may vary by the part you are cleaning. Here are some tips that apply to all products to get you started:

  • Use only a soft, lint-free cloth. Abrasive cloths, towels, paper towels, and similar items may cause damage to the item.
  • Disconnect your Apple product from any external power sources.
  • Disconnect any external devices and other cabling from the product.
  • Keep liquids away from the product.
  • Don’t get moisture into any openings, and don’t use aerosol sprays, solvents, or abrasives.
  • Do not spray cleaners directly onto the item.

Disconnect the display from power, from its connection to a computer, and from any external devices. Next use the cloth that came with your display or another soft, dry cloth to wipe dust from the screen. If additional cleaning of the display panel or case is required use a soft, slightly damp, lint-free cloth. Avoid getting moisture in openings. Don’t use window cleaners, household cleaners, aerosol sprays, solvents, alcohol, ammonia, or abrasives to clean the display.

Warning: Don’t clean the screen with a cleaner containing alcohol or acetone. Use a cleaner intended for use with a screen or display. Never spray cleaner directly on the screen. It may drip inside the display and cause damage.

When cleaning the outside of your Mac mini, first shut down your Mac mini and unplug the power cord along with other connected devices. Then use a damp, soft, lint-free cloth to clean the computer’s exterior. Avoid getting moisture in any openings. Do not spray liquid directly on the computer. Do not use aerosol sprays, solvents, or abrasives that might damage the finish.

Related: Curious Cat Gadgets BlogAmazon Kindle DX (the big one)Amazon’s Android Tablet, Kindle Fire, is Very Attractively Priced