As always, this kind of task can be done using a management
tool like pgAdmin
4 or via command (psql in this case).
Making this one a Superuser
for quickness.
And for the post, here’s the above being created using psql.
Install Postgres ODBC Driver
The ODBC driver must be installed on both ends – This is a
local set-up though, so I only need to install this once. You can use Stack
Builder to install it, or download the package on its own (link here) which
I’m doing below.
Click famous Next
button.
Accept License stuff.
Specify directory & we don’t need the documentation.
And then install!
Configure ODBC Data Source Settings
We need to point the ODBC driver on the SQL Server to the
Postgres database.
Run ODBC Data Sources
as Administrator.
Click into the System
DSN tab and click Add.
Select the PostgreSQL
Unicode driver.
Enter all details required as shown. I created a ‘sales’
database for this test which I’m connecting to.
Always worth it to test.
Create Linked Server to a Postgres Database
(Within SQL Server
Management Studio’s Object Explorer)
Expand Server Objects,
right-click Linked Servers and
select New Linked Server.
The Linked server
name at the top of the next window is what you’ll need to include in
queries, so choose that wisely.
Then choose a
provider for ODBC, enter any semi-relevant Product name, and then enter the Data Source name which you entered within the ODBC driver settings.
Enter the Postgres
login details as created above within this post.
I’m ignoring the Server
Optionstab for now and rolling
with the defaults.
When ready, take a breath, then hit OK!
Nothing will display if it’s successful, so have a go at
testing the connection.
We’re now linked with the Sales database that’s in Postgres!
There are many reasons why you’d need to delete old data from a table within a database, some of which include;
# GDPR (adhering to data retention policies) # Ever increasing tables becoming tougher to manage (e.g. a replication refresh is taking 2 days) # SQL Server Express limitations (10GB max database size) # The table is storing application errors & logs and you’ve been told it’s there to stay
Maybe it’s a one-off job for you, and the deletions can be done within a days work. Or, you might find yourself dealing with very large tables, deleting daily during a small off-peak window over the course of however long… this is a task that can come along with many variations, and if asked the question, how would you do it yourself? Well, “it depends!”.
I’ve run through a simple version of the task below, which also summarises a bunch of my other recent tech posts too. All links below are internal.
2. Look for one of the larger tables and pick your fancy.
3.Get row counts by date. For this example, I’m storing the row count information into a temp table. This deletion won’t take long so my session will still be active.
5. Get new row counts and compare against previous results.
Plus a few additional checks to verify.
Looking good – All data on orders beyond 7 years (1 January 2012 00:00) has been deleted.
When the initial task is done and all tables are meeting retention periods, we’d then need to consider things like scheduled SQL Agent jobs to maintain the tables every month.
Sp_whatnow? sp_who, sp_who2 and sp_whoisactive are stored procedures that allow you to view current users, sessions, and processes within a SQL Server instance. You’d want to see this for identifying things like blocking or checking general activity.
sp_whoisactive is definitely one of my favourite stored procedures. Created by Adam Machanic, this tool gives you an instant view of current SQL user process. Similar to sp_who/sp_who2 but doesn’t show system processes and gives you more useful information.
Below is a quick run-through of each of these stored procedures.
sp_who
This’ll get you information on all background and user processes going on in SQL at the time of execution. Info includes login names, hostnames, what kind of SQL command is running and which database it’s running against.
The first 50 results are system SPIDS which you’d never kill. Unless, maybe you identify the lazy writer is broken or something? I never have a need for sp_who personally as sp_who2 & sp_whoisactive do the trick.
An undocumented stored procedure by Microsoft, which means that it can be deprecated or modified without any notice. It shows the same as above but with some additional columns. I use this to kill off user connections on rare occasions but that’s about it really.
sp_whoisactive
You’ll see replication SPIDs such as your log reader agent, monitoring collections as well as any end users running reports / manual SQL queries. Info includes full SQL queries, query plans, hardware usage, tempDB allocations, blocks and more. This 4 minute video by Brent Ozar gives the perfect run-through:
To test this in less than 5 minutes, download sp_whoisactive, copy/paste the contents into a new query window and run the code against any database. It’s likely you will have no active user sessions in your test environment, so before we run it we’ll setup a blocking process. Run the following in sequential order, highlighting until each line break.
Now run a select statement in a new query window on the same table. The insert has a lock on the table until committed, so it’ll continue to execute as a result of being blocked.
Running sp_whoisactive now will look something like this.
As you can see I’ve highlighted the blocking sessions. For more information on encountering such events, see my blog post – Killing SQL Server Processes.
On rare occasions I’ve see my own exec sp_whoisactive SPID come back within the results. Likely it’ll depend on performance. You’ll know if your server is burning alive if running sp_whoisactive takes a long time to return results.
Most of the time I’m using sp_whoisactive with no parameters as shown above or with @get_plans = 1, but there are many that can be utilised depending on the task.
Another common use of this stored proc is to have it running as an agent job every 30-60 seconds, storing the results into a table. See my other blog post – Logging sp_whoisactive to a table for a run-through on that.
This is a follow-on post from Configuring MAMP (with WordPress) and is a guide on how to copy a website onto MAMP. Or I can phrase this by saying, copying your own live WordPress website and have it run locally on your laptop or PC. MAMP is a small app that allows your computer to easily run a local web server environment for WordPress.
The above can be used as a test environment for upcoming changes to your website if you don’t already have a Child Theme. This might also be used to have an isolated play-around area to see how your website looks in various forms.
I’ve attempted to illustrate the high-level move below. We’re downloading the MySQL database and WordPress files and throwing them into MAMP.
2.Open phpMyAdmin and select Export at the top menu bar.
3. A quick export in the SQL format is fine.
4. When you hit go you’ll begin the download of your MySQL database.
Download Your WordPress Files
There are many ways your website files can be downloaded, a few of which will be shown on your control panel.
Today, let’s choose Backup.
Go ahead for the download.
You’ll receive an email to where the backup has been saved to – grab it using File Manager or FTP. Also, the MySQL log file will be included as an attachment. Perhaps an idea for another blog to restore this one day.
Importing The Database
Before we start, the default maximum import size in phpMyAdmin is 2MB. My whytepete.com hosted version of phpMyAdmin came with a value of 50MB though.
My WordPress database size is only 5MB – this’ll of course vary depending on content volumes. I had to amend the php.ini file to allow the import.
1. Find the php.ini file(s). This is where a tool like Agent Ransack is useful for me.
2. Check MAMP to confirm running version. It’s likely the most recent version MAMP will be running as default anyway.
3.Before continuing, lets confirm the differences within each of those php.ini files. WinMerge does the trick, there’s only one difference as shown below between version 7.17 and 7.0.0. There are a few additions and changes to default values per major version by the looks of it.
4. Stop MAMP’s Apache & MySQL services and edit the relevant php.ini file. It’s 100% worthwhile reading through the comment section at the top.
5.Edit the following where required.
memory_limit Maximum amount of memory a script may consume (Default – 128M) post_max_size Maximum size of POST data that PHP will accept (Default – 8M). upload_max_filesize Maximum allowed size for uploaded files (Default – 2M).
The above should be set from biggest to smallest as reflected by their default values. If we amend the upload_max_filesize value to 25M, it’ll still be bound by the post_max_size value. This is what I done as my database fits within the 8M limit.
6.Open phpMyAdmin & create a new database.
7. Select your new database on the left-hand toolbar. The ‘selection’ of a database is a bit quirky, we just need to make sure it’s highlighted before importing.
8. Click on the Import tab and browse for your downloaded WordPress database.
9.Hit Go!
10. Success!
Configuring PHP & WordPress
Now that we have our database imported to MySQL Server, we need to configure PHP & WordPress.
1.First, lets look at the WordPress files you need from your download. If you have a full backup of your website directories, you’ll need to look in homedir > public_html.
2.All those files should be copied into MAMP > htdocs. Replacing existing files such as index.php.
3. Now, navigate to the website to see what’s happening. A popular error message this is.
4.Amend the wp_config.php file as shown. Taking note of the file path within the Notepad++ header.
The changes of note are:
define(‘DB_NAME’, ‘whytepete’); The WordPress database name. define(‘DB_USER’, ‘local_user1’); MySQL username with permissions over the WordPress database. define(‘DB_PASSWORD’, ‘unsafe123’); The associated password for the above user. define(‘DB_HOST’, ‘localhost’); The address of your server hosting MySQL. $table_prefix = ‘wp_’; You can find your table prefix by looking at all your tables within the WordPress database.
5.Refresh your browser and… HURRAY!
Navigation & WP-Admin Access
After getting your local website up and running, you’ll quickly realise links navigate to your website rather than the appropriate localhost/subdir. Typing URLs manually works fine though.
Also, if we navigate to localhost/wp-admin you’ll be redirected to your actual webpage WP-Admin login page. If you’re not redirected straight away it’ll at least happen when you try login.
The fix is to update the Options table within the WordPress database as shown.
